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?

 

[Uncle Gizmo]

Replication immediately springs to mind! I would have a look at this option, read as much as you can about it, because it is not straightforward! And there are some pitfalls. I have never used it myself, and everything I've read leads me to believe that it is something to be avoided If at all possible.

 

[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!

 

[Pat Hartman]

If the same table may be updated by the local database and the remote databases, you really need to use replication. I know it sounds complicated. That's because synchronizing two databases IS complicated. You won't be able to do it better yourself.

One thing you should be aware of is that creating a design master (the first step) will change all your autonumbers to random instead of sequential. That means that autonumbers may now be very large positive numbers or very small negative numbers. Neither would be suitable for display to users. You will need to generate your own "visible" numbers if you need them. To accomodate multiple replicas, add an additional column to each table that needs a sequence number. This new column will be used to uniquely identify the replica since the sequence numbers will now be duplicated. The two will be necessary to uniquely identify an "account", "student", "customer", or whatever.

 

[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!