Unanswered: Combine 2 databases - Autonumber problem
My employer has two accounting databases that do the same process. I have been asked to combine them into one database. Each database has relationships based on an Autonumber ID field, and the different databases would have duplicate IDs if I just combined them. I had an idea that I could just add 1000 to each ID in one database so that they were all higher than the ones in the other database, then combine them, but when I do all that, I loose the ability to use the autonumber function. Is there anything (VB code or otherwise) that could basically do what the autonumber function does going forward, but allow me to keep/manipulate the current ID data?
"One of these days I'll be able to design a well planned out database, instead of fixing someone else's unorganized mess."
Combining databases can be quite painful. Access has a feature called Replication built in, it uses a GUID to combine the tables. However you have to plan for replication. It sounds like it is too late in your situation. To keep the autonumber field and combine the databases you can write code to combine the databases into a third database. You will need to traverse the parent and child records in code. Alternatively, you can switch to GUIDs now for your primary and foreign keys to avoid future merging problems.
Does anyone see any problems with this working? I think it will because new records are entered using a form.
1) Change record type from Autonumber to Number.
2) Add the same number to the data in one of the databases to make sure it all exceeds any values in the other.
3) Make sure the field does not allow duplicates.
4) On the form, put code that updates the field in questions to something like max(id) +1 when you add a new line of data.
I think that might work, but would appreciate any advice if anyone has tried that approach before and had any problems with it. Thanks.