Results 1 to 5 of 5
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    Truncate Table


    I have an access database with linked SQL Server Tables. I run an access query on the table that holds roughly 5000 records, and it takes 5 minutes. I need this to be faster! Is there a "Truncate Table" statement that I could run in Access VBA, or would it be quicker if I create a SQL Stored Proc to Truncate the Table and run the stored proc from VBA?

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I suspect there is some useful suggestions for you in this link: http://www.fmsinc.com/microsoftaccess/performance.html

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #4
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Quote Originally Posted by jo15765 View Post
    I have an access database with linked SQL Server Tables. I run an access query on the table that holds roughly 5000 records, and it takes 5 minutes. I need this to be faster! Is there a "Truncate Table" statement that I could run in Access VBA, or would it be quicker if I create a SQL Stored Proc to Truncate the Table and run the stored proc from VBA?

    First, are you use the SQL Server Native client drivers?

    Second, need more detail about the query. Is is a simple SELECT from a single table with NO Where clause?

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Would also suggest check the basics - is the table indexed for the fields you are using for sorting/filtering? Has SQL server admin rebuilt the indexes? (bit like Access's compact and repair) - they can quickly become inefficient if there has been a lot of inserts and deletes. Is sql server busy doing something else for someone else?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 06-20-2014, 08:55 PM
  2. Truncate data
    By DCV0204 in forum Queries
    Replies: 6
    Last Post: 07-03-2012, 07:43 AM
  3. Replies: 2
    Last Post: 10-27-2009, 07:09 AM
  4. Truncate text
    By jgelpi in forum Programming
    Replies: 3
    Last Post: 07-16-2009, 12:44 PM
  5. truncate (?) values
    By ridgeview80 in forum Access
    Replies: 3
    Last Post: 06-02-2009, 02:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums