Synchronising Tables Within a Database

[Nick]

The organisation I am in is looking at a new database system, which must be
Access. It has many remote users that can not, due to many reasons, ever hook
up to the home office network (or any variation of an internet network). Nor
can we place our database on a web server or equivalent system.

So, I have come up with a way to export the remote database information into
excel and then import it into the Main Database in several tables called
“Transfer In-Out (Name)†table. Essentially, these tables are exactly the
same structure as the Main Database tables that they correspond with. I also
know how to added modification dates to the tables in order to identify when
a record was last modified (thanks to this forum and the MS website).

New records are easy to deal with, I just use an amend query and add them to
the Main Database Tables. The problem is modification to existing records.

Is there a way, and how would you do it, to synchronise two tables WITHIN
one database?

NOTE - this is a follow on from a question I am asking about Database
Design. I am muddling through the problem, but hopefully there will be light
soon!

 

[John Spencer]

You can update records in table1 from records in Table 2, but how do you
know the records in table 1 are not more current than the records in
table 2? Are you going to using the modification date to determine the
winner?

The simplest method would be to DELETE records from the MasterTable and
replace them with records from the remote table if the ModificationDate
in the MasterTable is earlier than the ModificationDate in the RemoteTable

DELETE
FROM MasterTable
WHERE MasterTable.PrimaryKey IN
(SELECT M1.PrimaryKey
FROM MasterTable as M1 INNER JOIN RemoteTable
On M1.PrimaryKey = RemoteTable.PrimaryKey
WHERE M1.ModificationDate < RemoteTable.ModificationDate)

Then just run the routine you are using to add new records.

Otherwise, you will need to use something like

UPDATE MasterTable INNER JOIN RemoteTable
ON MasterTable.PrimaryKey = RemoteTable.PrimaryKey
SET MasterTable.Field1 =[RemoteTable].[Field1]
, MasterTable.Field2 =[RemoteTable].[Field2]
, MasterTable.Field3=[RemoteTable].[Field3]
, ... ///Repeat for all the fields except the primary key///
WHERE MasterTable.ModificationDate < RemoteTable.ModificationDate

If you wanted to do individual fields you could, but you would require
many queries (one for each field) that would look like the following.

UPDATE MasterTable INNER JOIN RemoteTable
ON MasterTable.PrimaryKey = RemoteTable.PrimaryKey
SET MasterTable.Field1 =[RemoteTable].[Field1]
WHERE MasterTable.ModificationDate < RemoteTable.ModificationDate
AND (MasterTable.Field1 <> RemoteTable.Field1
OR (MasterTable.Field1 is Null and RemoteTable.Field1 is Not Null)
OR (Mastertable.Field1 is Not Null and RemoteTable.Field1 is Null))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Douglas J. Steele]

Alternatively, see my November, 2003 "Access Answers" column in Pinnacle
Publication's "Smart Access" for how to create a single query that will
update matching rows and insert new rows. You can download the column (and
sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You can update records in table1 from records in Table 2, but how do you
know the records in table 1 are not more current than the records in table
2? Are you going to using the modification date to determine the winner?

The simplest method would be to DELETE records from the MasterTable and
replace them with records from the remote table if the ModificationDate in
the MasterTable is earlier than the ModificationDate in the RemoteTable

DELETE
FROM MasterTable
WHERE MasterTable.PrimaryKey IN
(SELECT M1.PrimaryKey
FROM MasterTable as M1 INNER JOIN RemoteTable
On M1.PrimaryKey = RemoteTable.PrimaryKey
WHERE M1.ModificationDate < RemoteTable.ModificationDate)

Then just run the routine you are using to add new records.

Otherwise, you will need to use something like

UPDATE MasterTable INNER JOIN RemoteTable
ON MasterTable.PrimaryKey = RemoteTable.PrimaryKey
SET MasterTable.Field1 =[RemoteTable].[Field1]
, MasterTable.Field2 =[RemoteTable].[Field2]
, MasterTable.Field3=[RemoteTable].[Field3]
, ... ///Repeat for all the fields except the primary key///
WHERE MasterTable.ModificationDate < RemoteTable.ModificationDate

If you wanted to do individual fields you could, but you would require
many queries (one for each field) that would look like the following.

UPDATE MasterTable INNER JOIN RemoteTable
ON MasterTable.PrimaryKey = RemoteTable.PrimaryKey
SET MasterTable.Field1 =[RemoteTable].[Field1]
WHERE MasterTable.ModificationDate < RemoteTable.ModificationDate
AND (MasterTable.Field1 <> RemoteTable.Field1
OR (MasterTable.Field1 is Null and RemoteTable.Field1 is Not Null)
OR (Mastertable.Field1 is Not Null and RemoteTable.Field1 is Null))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

The organisation I am in is looking at a new database system, which must
be Access. It has many remote users that can not, due to many reasons,
ever hook up to the home office network (or any variation of an internet
network). Nor can we place our database on a web server or equivalent
system.

So, I have come up with a way to export the remote database information
into excel and then import it into the Main Database in several tables
called "Transfer In-Out (Name)" table. Essentially, these tables are
exactly the same structure as the Main Database tables that they
correspond with. I also know how to added modification dates to the
tables in order to identify when a record was last modified (thanks to
this forum and the MS website).

New records are easy to deal with, I just use an amend query and add them
to the Main Database Tables. The problem is modification to existing
records.

Is there a way, and how would you do it, to synchronise two tables WITHIN
one database?

NOTE - this is a follow on from a question I am asking about Database
Design. I am muddling through the problem, but hopefully there will be
light soon!

 

[David W. Fenton]

The organisation I am in is looking at a new database system,
which must be Access. It has many remote users that can not, due
to many reasons, ever hook up to the home office network (or any
variation of an internet network). Nor can we place our database
on a web server or equivalent system.

I have posted many times in many different forums about how to
synchronize two databases in code. There are two easy parts to the
process:

1. unmatched records in the source database: this means the records
were added, and should be appended to the destination database.

2. unmatched records in the target database: this means the records
were deleted, and should be deleted in the destination database.

These are very easy to figure out using a simple outer join.

The hard part is efficiently updating records that have changed. You
don't want to write SQL that updates every single field, but you
also don't want to walk through a recordset. The efficient way to do
this is to run a SQL update for every field in the two tables where
the values are not equal. This is accomplished by walking through
the Fields collection and writing a SQL string with an appropriate
WHERE clause, which would result in something like this:

UPDATE Table1
SET Table1.Field1=Table.Field1
WHERE Nz(Table1.Field1,"")<>Nz(Table2.Field1,"")

Obviously, you need some logic to test the underlying field type,
since you want the WHERE clause for numeric fields to look like
this:

WHERE Nz(Table1.Field1,0)<>Nz(Table2.Field1,0)

But that's the basic idea:

1. run the two outer join queries, one to append new records, one to
delete old records.

2. walk through the Fields collection and issue a SQL Update for
each column.