Merging Databases

[Lyncroft]

I've 2 databases used essentially for mailing purposes, to be merged into one.

Each database is the same - they have a table using an autonumber for each customer. This table is linked by the autonumber to another table that lists products the idea being that for mailing purposes you select a product and out come the relevant customers.

The problem is when I paste append the the 2nd databases customer tables into the 1st database table the autonumber changes accordingly based on the last entry in the table. Consequently once I've pasted the 2nd databases products table into the 1st Databases the linkages don't tally up correctly because the ID number is now different.

I'm not helped by the fact that some records have been deleted so the autonumber sequence isn't a smooth sequence if you see what I mean?

Any ideas? Is there a way of not making the numbers change when I paste append............he says grasping at straws!!!

 

[The_Doc_Man]

If this is a one-time-only operation, try this:

First, notify any and all users to go take a long lunch break.

Next, make a really good backup copy of your destination DB, the one to which you wish to add records from the other, or source, DB. Copy your source if you wish, though what I suggest here should not muck the source.

Now, open the destination DB. Find the highest number existing in the main table in the destination database. (The one where this troublesome number is an autonumber rather than a simple long integer.) Kust open the table, sort by the number if not that way already, and use the nav buttons to drop to the last record.

OK, now you have a number. Add one or two to it. Any small, convenient number.

Now, for every table in the source database, build a table link from the destination that looks at the corresponding source table. For each table, create an ad-hoc Append query from the source table to the destination table. For all fields in each table, just do a straight copy. EXCEPT for the autonumber field. For THAT ONE FIELD, import [Destination]![SeqNo] = [Source]![SeqNo] + {pre-determined-offset}.

Do that for the corresponding field in every table to be imported from the source.

OK, this next step is incredibly important. COMPRESS the destination database. The reason you need this is because that will force the Autonumber to reset to take into account the new records you just added.

If, on the other hand, this is going to be a frequent event, I strongly suggest you reconsider this design.

 

[Lyncroft]

Hello there Doc Man - thanks for taking the trouble.

I'm not 100% clear at the moment. There are no front end/back end issues so I've got one table called Address with the autonumber and another table called Address1, also with the autonumber all within one database. What I want to do is append Address1 to Address but not to alter the autonumber.

This is where I get lost. You wrote:

"for every table in the source database, build a table link from the destination that looks at the corresponding source table. For each table, create an ad-hoc Append query from the source table to the destination table. For all fields in each table, just do a straight copy. EXCEPT for the autonumber field. For THAT ONE FIELD, import [Destination]![SeqNo] = [Source]![SeqNo] + {pre-determined-offset}"

For each table is this done within just one append query? If so am I right in saying I lay out the append query so that all the field apart from the ID are appended as normal from Address1 to Address? The last bit I don't understand:

EXCEPT for the autonumber field. For THAT ONE FIELD, import [Destination]![SeqNo] = [Source]![SeqNo] + {pre-determined-offset}"

Where do I put this code and what exactly does "pre-determined offset" mean and how is it shown?

Thanks again - sorry for being dim!!

 

[Lyncroft]

Had a look at the append query help and it would appear that when you set the append up if you drag the auto field to the query grid (as opposed to using the asterisk) then the autonumber won't change when you run the query. Seems to work (I think!)

 

[The_Doc_Man]

Thanks for the amplification, Pat. Lately I've been a bit hasty in my replies due to having the usual Christmas rush at a government site. I.e. the gov't managers want all their reports done before the holidays 'cause they don't want to come in themselves. One of the down sides of the job, though I must admit it doesn't happen that often.

Back to the problem.

Lyncroft, I guess I should tell you the overview of what you need to do to merge the tables.

I am presuming that there is really only a single number that is autonumbered that gives you problems. It is the record number that is a prime key in the parent table on which you base all of your DB's relationships. In order to keep the relationships of the incoming data intact, you have to do the following things.

1. Determine an offset that you will add to the incoming parent table to keep the autonumbered field unique in the receiving table.

2. Using an append query, add the contents of the parent table, keeping all other fields intact BUT add the offset to the field that is an autonumber field. If you explicitly reference that field, you CAN set it to something other than an auto-incremented number. It is only when you create a new record and DON'T reference the field that it autonumbers.

3. Now, for each incoming child table that has a relationship based on that number that was autonumbered in the parent table, do EXACTLY THE SAME THING AS STEP 2, using EXACTLY THE SAME OFFSET. Append each child-table record with the offset added to the (in this case) foreign key.

Doing it in this order preserves the relationships and honors the rules about relational integrity for adding records - UNLESS the records you are adding are one-to-one, not one-to-many. That one-to-one case happens sometimes. If so, temporarily uncheck the referential integrity while you are doing the append. But if the relationships are really one-to-many, you don't need to do anything about RI.

NOTE: If the child tables ALSO have autonumbered fields, what you do about them depends on whether THOSE autonumbers are foreign keys elsewhere in your DB. I.e. if there is a grandparent-parent-child relationship. If you don't have such a relationship, then the answer is you do nothing and just let the child tables autonumber to their hearts' content.

4. Now repair and compress the database.

You are done.

Hope that cleared it up a little more for you.

 

[Lyncroft]

Thank you to you both for taking time out, particularly at this time of year.

I'm almost there and I now understand the logic of whats going on. My only stumbling block is on the append query. I'm putting table address into address1 with ID being the problem field so would the code be:

[address1]![ID] = [address]![ID] + 700

I'm embarrassed to say this - but which row of the append query does the code go? I seem to have tried all combinations but none work properly.

Promise I won't bother you anymore - honest!!

 

[The_Doc_Man]

In the Append query, do the following:

In the column corresponding to [ID]

In the row marked Field, use [ID] + 700

In the row marked APPEND TO, use ID

 

[Lyncroft]

Doc_Man - I just got it to work and I even managed to get it to work before your last tip. Its a useful tip to know this one.

Thanks for your help again and a Merry Xmas to you!!!!!