Updating tables with missing keys

[graffy_76]

Hello,

I'm designing a database that is used both in the office and field and it has two tables - an "office" table and a "field" table. The office table is a list of addresses, cities, and counties, each with a custom-designed key field. The field table contains equipment information at each location specified in the office table.

What I want to do is set up the database so that field personnel can copy an updated office table (with new locations added or old locations removed) into their databse and click something and have the tables "resync" with each other - the field table automatically adding new records to match those in the office table or delete records it has that it doesn't find in the office table...

Any thoughts or helpful links?

 

[graffy_76]

I suppose replication is to the typical Access user what Ludicrous Speed was to the Spaceballs?

In any case, given a brief review, I can see why you'd not want to use it, but it appears to be my only option. I just need to implement effective damage cnotrol strategy before attempting it, I suppose. I was interested in being able to synchronize tables within one database, but since each "field" computer is basically using a replica of the main "office" database, it seems replication would work as well...

Still open to other options, of course, but thank you for the suggestion!

 

[beliado]

I might be thinking to simplistically here but...

If the field personnel are the only ones who need the updates, and all the changes are being made my office personnel, why not just link the two tables from the office database to the field database? In other workds, in the field database [file][get external data][link tables] and point to the office database. This should work fine unless field personnel are responsible for making table updates as well, but from your original comments, it doesn't sound as if they are.

 

[graffy_76]

Unless I miss my guess, if I link tables, I have to have a network connection to reach the office tables while in the field - which isn't possible in this case.

I just tried replication and it seems to do the job quite well. It is a bit more than I need, but the effort to perform the same task programmetrically doesn't strike me as being particularly pleasant or easy. Anyway, I think if I lock the "office" table for field personnel and the "field" table for office personnel, it will eliminate most problems.

Thanks for the help!