Merging Databases

[acrocker3434]

Hello-

We are using an Access database for off-site data entry. We therefore have one master database we keep in the office and then a "template" version -which is identical to the master version except no records - we bring to each site, do the data entry and then merge that data back into the Master database.

Can anyone help suggest ways to make the data merging process more efficient. The database has approx 12 tables, so I dont' want to create an append query for each one every time. Is there a way to just "merge" two identical databases??

THANKS

 

[neileg]

As a start, have a look in Access help on replicating and synchronising databases.

 

[acrocker3434]

Replica sounds like a good idea

I found this article
http://articles.techrepublic.com.com/5100-10877_11-6154732.html

and realized that i should have made this a Design Master database with "replicas" from the getgo. However, instead I just made a blank database and copy/pasted it and renamed it. I did this 15 times, so now i have 15 idential databases with unique records.

Any thoughts on how to get these back into comprehensive database?

Thanks

 

[neileg]

The usual issue is around autonumber primary keys. I would guess that you have duplicate PKs in your 15 databases?

 

[aikea]

Import tables (either link or data) from your other database.

Create a query that insert each record from the imported table to your master database table. Make sure you do not select the ID field.

New inserted record will have its own ID assigned by Access if you are using Auto Number type as primary key.

----------------

And from now on, use replicas only.

 

[neileg]

Import tables (either link or data) from your other database.

Create a query that insert each record from the imported table to your master database table. Make sure you do not select the ID field.

New inserted record will have its own ID assigned by Access if you are using Auto Number type as primary key.

That doesn't address the issue of aligning the new PK with FKs in related tables, I'm afraid.

How I would tackle this:
If your 15 databases are all starting at 1 for the autonumber, then you are going to get duplicates in the ID field. To get around this you need to do something that makes the ID from each database different. You could do this by appending another 2 digits in a new calculated field in each table. You do this to all keys, primary or foreign and this keeps them syncronised. For example from your first database, take the ID and multiply by 100 and add 1, in the second multiply by 100 and add 2, etc.
12345 would become
1234501 from the first databse
1234502 from the second
1234503 etc
1234504 etc
1234505 etc
You can then append these records into one table. You will find you can append to an autonumber field.

 

[acrocker3434]

tables merged

thanks for the helpful hints.
I ended up creating new primary keys for each database - using your suggestion and appending numbers to the original primary key. I then found a great free online program used to merge tables. i had to do one table at a time, but it worked great across 35 different databases.

 

[Simon_MT]

I personally found a Terminal Server solution so that easier. There is too much other information in the database that is common that replicating was a not starter. That doesn't include any ancilliary images or documents that relate to both sites.

The database can be updated with all its integrity remotely and locally.

Simon