We help IT Professionals succeed at work.

Acc ess VBA  synchronize two tables

Murray Brown
Murray Brown used Ask the Experts™

I have two Access tables (Table1 and Table2) of the same structure in two different offices.
Each month I need to "synchronize" the data so that they each have all the information. So I need all the records in Table1 that aren't in Table2 to be added to Table2 and all the records from Table2 that aren't in Table1 to be added to Table1.
How wouls I do this? What techniques would I use?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Are the two computer on the same network. If yes then the best approach would be to split your database into backend and front end. Say backend on a central location and install frontend on each computer. No synchronizing required.

Add two extra fieldseach table. Let's call them fldTable and fldIndicator. Set the default value of that field to 1 and 2 in table1 and table2 respectively.

Then add the following queries in each DB:

Select Col1,Col2,Col3,[fldID] & [fldTable] As fldCheck From Table1
Select Col1,Col2,Col3,[fldID] & [fldTable] As fldCheck From Table2

NB: I am assuming that each table has a primary field set to autonumber
      Let's call the queries Q1 and Q2
      [fldID] & [fldTable] concatenate the id field and the table number next we are going to insert this value in fldIndicator the other table. This means that fldIndicator will indicate if a particular ID from the other table has been entered. To do this we can use the following queries:


    INSERT INTO Table1(fldIndicator,Col1,Col2,Col3)
    SELECT fldCheck,Col1,Col2,Col3 FROM Q2 IN 'Directory path/DB2.mdb'
    WHERE NOT IN Q2.fldCheck(SELECT fldIndicator FROM Table1)


    INSERT INTO Table2(fldIndicator,Col1,Col2,Col3)
    SELECT fldCheck,Col1,Col2,Col3 FROM Q1 IN 'Directory path/DB1.mdb'
    WHERE NOT IN Q1.fldCheck(SELECT fldIndicator FROM Table2)

NB:- The query is run on a query in an external DB
Murray BrownASP.net/VBA/VSTO Developer