putting data together with an autonumeric field

dirkvw

Registered User.
Local time
Today, 17:48
Joined
Oct 11, 2013
Messages
15
I have 2 databases with the same structure, but different data.
I want to put all the data in 1 database.
The problem is that the most important table (clients)has an autonumeric id field, that is used to link the records to the fields of many other tables;
so, I cannot delete or change that field without losing connections.
Is there a way (VBA?) to work around this problem?
TIA
Dirk
 
assuming this is a one time exercise and you are appending db2 to db1

1. in db1, add an additional field 'oldautonumber' as long (and indexed) to your customer table
2. append your customers from db2 to this new table, ensuring you populate the oldautonumber field with the autonumber field from your old table
3. now append your other tables from db2 and where you reference the customer autonumberfield by having say invtable linked to customers on invtable.customerid=customers.oldautonumber and for the invtable.customerid, use the customers.autonumber value
 
Are we to assume that data from the two separate databases would have duplicate autonumber ids if combined?

This is also why I hate autonumber and build my own 'autonumber' field in code.
 
Yes, there are duplicates.
You're right, but sometimes I'm a bit lazy!
 

Users who are viewing this thread

Back
Top Bottom