Merge database

[Guest]

I have looked all over this site and I haven't found anything with my
particular problem, although I know it has been asked before. I need to
transfer information from my old database to the new one. The first problem
is that the fields are not the same name or size. The second problem is that
I have auto numbers in the old database and I need them to be the same in the
new and the new entries start counting from the last one from the old.

The only thing I have tried to do is copy all my information from the old
database and paste it into an excel sheet and then proceed to copy it to the
new database. This works for the problem with the field sizes and names but
when I tried to copy the autonumbers it gives me an error that autonumbers
cannot be edited. I have to have it has an autonumber so turning if off is
not an option unless you have a code that will do the same as an autonumber.

Help,
Tina

 

[Danny J. Lesandrini]

Tina:

Below is a link to an article I wrote for DBJ and it includes a download of code. However,
this is not a Merge Utility. It will not merge your databases. It is only an example of how
one might code the problem to handle new autonumbering of old records.

http://databasejournal.com/features/msaccess/article.php/2236471

For example, if your databases have Orders and OrderDetails linked on OrderID, but both
databases start out with OrderID = 1, then the data from MDB2 that you want to fold into
MDB1 needs to have its OrderID increased by (at least) the highest number used in MDB1.
This means that all the corresponding OrderIDs in the OrderDetail table need to be reset
as well. My sample shows how this might be accomplished.

Also, there might be tables like status or category. If, for example, the category table had
one named "Drink" in one and "Drinks" in the other, you could manually link these two. If
the Status table in one has Pending=1 and the other has Pending=5, you can likewise map
those. This gets confusing, but download the sample app and give it a whirl. It might help,
but remember, it's not a merge tool so don't expect it to do your work for you. It' just a guide.

 

[Guest]

Thank you Danny, unfortunately I didn't understand that article at all. I
guess I am having a stupid day or I'm just that stupid.

"2. Store largest EmployeeID from Site A in variable named lngMaxSiteA_ID" -
what do you mean by that? I don't understand the term variable. Do you mean
field?

For number 3, I don't have to worry about cascades because it is an invoice
number I am working with and its not connected to anything but the job Id and
they are in the same table. So I go to the next step and you lose me again.
I don't know how to run an update query. I've never had to do in until now.

I think I need too much help.

Right now my only option is to manually key in all my old records to the new
database and that will take for ever.

 

[John Vinson]

I have looked all over this site and I haven't found anything with my
particular problem, although I know it has been asked before. I need to
transfer information from my old database to the new one. The first problem
is that the fields are not the same name or size. The second problem is that
I have auto numbers in the old database and I need them to be the same in the
new and the new entries start counting from the last one from the old.

The only thing I have tried to do is copy all my information from the old
database and paste it into an excel sheet and then proceed to copy it to the
new database. This works for the problem with the field sizes and names but
when I tried to copy the autonumbers it gives me an error that autonumbers
cannot be edited. I have to have it has an autonumber so turning if off is
not an option unless you have a code that will do the same as an autonumber.

Help,
Tina

YOu can use File... Get External Data... Link to link to the old
database, and then run Append queries to migrate the data from the
linked tables into the new tables. An Append query will copy an
autonumber (indeed it's the only way I know to control the value of an
autonumber field).

John W. Vinson[MVP]

 

[Guest]

Thank you very much John. With a little tweaking, this worked perfectly.