How do I synchronize database records

[Colin Cox]

I have an Access 2003 database containing approx 3000 records in linked
tables. I would like to be able to take a copy of the database to another
location where approx 9 or 10 records would be updated. I would then like to
merge those records back into the main database on my return. I cannot just
use the copy on my return as records in the original would be updated during
my absence. Is this at all possible?

Thanks

 

[Jeff Boyce]

Colin

Are you quite confident that the "9 or 10 records" that were updated at your
other location would NEVER also be updated in your main/primary database?
If that could happen, how will you handle that?

One approach might be to add a field on your table(s) that contain a
date/time value for [LastUpdated]. Then, when the prodigal db returns, you
could use a query to find all records (?in all tables) that have been
updated since the date you made the copy, then use those to update their
main/primary counterparts.

JOPO (just one person's opinion...)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[David W. Fenton]

I have an Access 2003 database containing approx 3000 records in
linked tables. I would like to be able to take a copy of the
database to another location where approx 9 or 10 records would be
updated. I would then like to merge those records back into the
main database on my return. I cannot just use the copy on my
return as records in the original would be updated during my
absence. Is this at all possible?

Sounds like an ideal candidate for Jet replication, but you'd have
to give more details (e.g., are you carrying a laptop to the other
location, or are you planning on carrying the database between
locations via sneaker net?) before I'd make the recommendation for
certain.

I've been using Jet replication in client apps since 1997 and find
it very useful for the limited number of scenarios where it's
extremely useful. Many of the scenarios where it was invaluable 12
years have vanished, supplanted by Windows Terminal Server/Citrix
solutions. I'm not suggesting that's the answer in your case, just
that I still use Jet replication in lots of apps, just not as
extensively as in the past.

I've also programmed "manual" synchronization between master/slave
and multi-master databases, and it's orders of magnitude more
difficult to do properly than Jet replication (though simple
master/slave is not so hard).

 

[Colin Cox]

The database would be copied onto a laptop from where it will be accessed at
the remote location. No record would be updated at both places at the same
time.

 

[David W. Fenton]

The database would be copied onto a laptop from where it will be
accessed at the remote location.

As long as the laptop is carried to the remote location and synched
via LAN, that's fine.

No record would be updated at both places at the same
time.

Then why do you need Jet replication at all?

 

[Colin Cox]

Sorry for the delay, have been away for a few days. The database will be
copied onto the laptop for use at the remote location where there is no lan
and would then be synched on it's return.

 

[David W. Fenton]

The database will be
copied onto the laptop for use at the remote location where there
is no lan and would then be synched on it's return.

As long as the synch is from the laptop in all cases, that's not a
problem.

That is, you create a replica on the laptop, synch it with the
home-based replica, carry the laptop to the remote location, synch
it from the laptop with the remote replica, carry the laptop back to
the home base and synch back with the home-base replica.

The key thing you want to avoid is copying the home-base replica to
the laptop each time you want to synch with the remote site. Once
you have a replica on the laptop that is synched with the remote
site, it has to stay there, and be synched back with the home-base
replica in place on the laptop. It can then be used in the future as
the conduit for carrying updates between the locations.