Results 1 to 4 of 4
  1. #1
    JKnightly is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2015
    Posts
    4

    Updating local table records only if a linked table has changed

    Hello,



    I have a few tables that contain a large number of records ~200,000 in our ERP database, reading from these through a linked table has poor performance and even using a pass-through query my performance could be better. Performance from local tables seems to be the best, so I have created local copies of my linked tables using make-table queries. My question is this, is there any way to compare the data from my linked table to my local table and only update the changes? I have a macro to run the make-table queries to update my local tables but it recreates the entire local table and the performance is way less than desirable. Any ideas?

    Thanks for any input.

    - Jonathan

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I am not aware of a feature for bidirectional synchronization in Access. Your best bet would be to use WHERE criteria on your query that retrieves data from the server. It has been my experience that humans do not review 10's of thousands of records in a single session.

  3. #3
    JKnightly is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2015
    Posts
    4
    All of the actual used queries have WHERE criteria but I trying to come up with a way of keeping these local tables, which all the queries are based on, up to date.

  4. #4
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    If you maintain that active link between the two tables, you could compare the amount of rows. You could dump both tables to a temp csv file and checksum/hash/md5 those files for differences.

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

Similar Threads

  1. Replies: 3
    Last Post: 01-01-2015, 05:25 PM
  2. How to Create local copy of linked Table
    By behnam in forum Programming
    Replies: 3
    Last Post: 11-20-2014, 05:49 PM
  3. Updating Linked Table
    By cbende2 in forum Access
    Replies: 1
    Last Post: 07-29-2014, 01:43 PM
  4. Replies: 3
    Last Post: 11-04-2012, 09:25 AM
  5. Replies: 0
    Last Post: 05-21-2012, 11:54 PM

Tags for this Thread

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