Results 1 to 2 of 2
  1. #1
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    313

    Bulk Copy Table from SQL Server to Access Temp Table


    My users get much better performance running a report that uses tables linked to an Access Backend than they do from SQL Server. However, all the data is maintained on SQL Server. I would like to have a VBA script that copies selected tables from SQL Server to an Access Backend at the beginning of the day and then have the users reference the tables on Access for performance. I am currently using an Access query to append the data to an empty Access table successfully. However, it takes about 5 minutes to load all the tables this way. Is there a faster way to do this? The temp tables have no keys or indexes since they don't ever get updated. Would creating an ADO Recordset be faster? Is there a way to do a "bulk" load? Thanks, Eddie

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I would say you are at the peak. The append query is all you have.
    Now , you could build a timer form (I did) that appends the data at night, and when you come in ,in the morning, all the data is refreshed.

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

Similar Threads

  1. Replies: 7
    Last Post: 08-09-2013, 12:04 AM
  2. Updating main table from temp table AND form value
    By shabbaranks in forum Programming
    Replies: 8
    Last Post: 05-01-2013, 07:18 AM
  3. delete temp table
    By slimjen in forum Queries
    Replies: 1
    Last Post: 07-19-2012, 06:35 AM
  4. Replies: 3
    Last Post: 08-13-2011, 11:50 AM
  5. Query for temp linked table
    By compooper in forum Programming
    Replies: 8
    Last Post: 06-30-2011, 06:53 AM

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