Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    15

    Question 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."

  2. #2
    Join Date
    Feb 2004
    Location
    Columbus, OH
    Posts
    36

    Smile Replication

    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.
    Greg Finzer
    http://www.kellermansoftware.com
    .NET Components

  3. #3
    Join Date
    Sep 2004
    Posts
    15
    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.

  4. #4
    Join Date
    Feb 2004
    Location
    Columbus, OH
    Posts
    36

    Smile Maxid+1

    The problem with using that approach is that you could conceivably run into problems in a multi-user environment where two people are adding records at the same time.
    Greg Finzer
    http://www.kellermansoftware.com
    .NET Components

  5. #5
    Join Date
    Sep 2004
    Posts
    15
    I thought of that, but fortunately this part of the data entry is usually done by only one person.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •