Merging databases (Xp sp1)

[Darsha]

I've just finished a draft database for storing info gathered from surveys by a number of users. The users will need their own copy of the database to enter their info and ultimately these will need merging together.

I know I could import the tables into a master database and run some append queries but I think I'm going to be ask to provide some kind of merge functionality. This is a big step for me and I have little idea where to start so any suggestions would be gratefully received.

The database comprises 1 main table and 17 child tables, linked by a textual, manually entered ID (i.e not autonumbers). Theoretically, this id will be not be duplicated in the copies of the database but I guess its not impossible, so before running any query I would need to check for the existence of the id and ask the user if they want to overwrite existing information or discard the new information.

[WendellB]

Is there a reason for not using a single back-end that users connect to? That would eliminate the need for a merge - in generally databases are designed to be multi-user. Of course if nobody is connected to a LAN, then things get pretty complicated. There is a feature called replication that can be used to synchronize (merge) databases that are only loosely connected, but it introduces some significant complications if you plan to use it long term. Otherwise you are pretty much stuck with running append queries as you suggested. If you want more details on replication, post back.

[MarkLiquorman]

I'd rethink how you assigned that unique ID number. Why bother spending time worrying about duplicates? Rather than use a sequential autonumber, use a random autonumber. This will make it highly improbably that you will get duplicates (something like 1 in a billion).

[Darsha]

Maybe I wan't too clear,
The primary key in the parent table is not an autonumber. Its as a text string (I had no choice in this). The value is then the foreign key in all the child tables. The only way duplication could occur is if someone uses the key by mistake and then doesn't delete the record before and merging (hwoever accomplished) occurs.

It is likely this database will be used by half a dozen users (maybe more) in office locations not connected with a wan or out in the field on a laptop (again) so I'm being asked to provide functionality via a simple idiot proof interface (for an inexperienced access user) to merge these files back together. The kind of thing where the user would select a 'master' database, choose another to be merged, click a button and hey presto, its done, with the option to quit if any errors occur.

I was just wondering if anyone had an example of this kind of thing.

One more question, the records in the child tables currently have a unique reference via an autonumber ( a result of me always answer yes when access suggets it). I'm not sure whether they're needed but say I leave them in, would it be better if they were random instead of sequential.

[MarkLiquorman]

You were clear enough, I just suggested you rethink the original decision. If the risk of duplicates is real, then it is better to adopt a strategy that will prevent them, rather than one that fixes them later. Another route is to create a primary key that is based on some sort of unique site code, perhaps even a 2-field key that uses a site code and an autonumber.

[WendellB]

Since you have a situation where databases are not really connected, you should probably look seriously at replication. Developing an idiot-proof merge process is akin to building your own version of replication. It may be overkill in your situation, especially if the data never needs to be modified after it has been initially entered, but it has all the mechanisms you need. You might find our replication tutorial a useful starting point. Feel free to post questions about it as well - several frequent loungers have experience with it.

[Darsha]

I'm not even sure if replication would really fit the bill either. For now I think I might avoid the question of what to do with numerous copies of the database until they actually exist and i have to deal with them <img src=/S/igiveup.gif border=0 alt=igiveup width=31 height=23> Thanks for all your suggestions guys.