The attached items were written some time ago in response to various questions from forum members including several posts by AWF member jaracek
I'm posting the information here to make it easier to find it in future
As with most processes in Access, there are several ways of synchronising data.
Whichever method is used it is recommended that a backup is made before synchronising with external data in case any problems arise.
The best solution for an individual situation will depend on various factors including:
• the number of records to be transferred or modified
• the number of records containing null values
• speed and file size
It will also depend on whether or not the import table has a primary key field or not.
The example application compares 9 different approaches to synchronising data.
Two PDFs explain each process in detail
a) Part 1 - Four approaches suitable for use when the import table has a primary key field e.g. another Access or SQL table
b) Part 2 - Another 5 methods are provided for use where there is no primary key in the import table. Typically this would be when importing from an Excel or CSV file.
Each method is used to synchronise a table tblData
originally containing 200 records with another table tblImport
containing 20,000 records.
Several of the original records have missing or changed data that needs to be updated.
In addition, the code measures the time taken and the increase in file size for each method.
Here are the average results of both sets of sets:
For the purpose of this example, it is assumed that:
• the records in tblData
are being updated from tblImport
contains ALL records that should be imported to tblData
The latest version of this article & example app can also be found at: http://www.mendipdatasystems.co.uk/s...a-1/4594514001
I also recommend an interesting approach suggested by MajP
in response to another question by jaracek - see https://www.access-programmers.co.uk...7&postcount=69
MajP's solution is intended for situations where there are many fields containing nulls in one or both tables involved in synchronisation