Results 1 to 3 of 3
  1. #1
    kagoodwin13 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Feb 2012
    Posts
    181

    Update from very large ODBC linked table

    I have a linked table to an ODBC data source. This table is very large, 112k records and and 68 fields (7m data cells).



    Currently I have a job that deletes local copy of table, and inserts all data from linked table to local table. This job takes over an hour to run. This makes me think there could be a more efficient way.

    I am working around by connecting the table into an Excel file, refreshing the Excel file (about 5 minutes), and importing the table into the local table.

    What is the difference in the querying between the Excel file and the complete insert? Is there a way to configure the update query to only look for changes or new rows? It seems like the ODBC has to download the full table before running any queries on it in Access.

    Thanks for your help!

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Where is this linked table located? What is the dbms where the table resides?

  3. #3
    kagoodwin13 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Feb 2012
    Posts
    181
    The linked table is a cloud database table, connected via ODBC. The DBMS is probably Oracle. I am transferring this query from Access to SQL Server, which will dramatically improve local querying performance.

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

Similar Threads

  1. Replies: 3
    Last Post: 12-17-2015, 07:01 AM
  2. Replies: 13
    Last Post: 10-04-2015, 09:52 AM
  3. runtime error 3157 odbc update on a linked table failed
    By KAMALRAJ.PPS in forum Programming
    Replies: 2
    Last Post: 12-02-2014, 09:43 AM
  4. Replies: 1
    Last Post: 01-22-2011, 12:23 PM
  5. Replies: 0
    Last Post: 11-30-2010, 12:23 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