Merging Access tables and relations

You can import data from one database into another. Use File...Get External Data....Import. The wizard will ask where the database is. Then it will ask what you want to import. At some point, you will see a button or something that says Options (or something similar). If you look in there you will find an option to import the Relationships also.

hi kat,

thank you but this is not exactly what I need...
'cause Access imports the data correctly, but not into the existing tables

like:

myDB1: Table "Customer"
myDB2 (which I want to be included in myDB1):
-> after importing
myDB1: Table "Customer" (orig.), AND Table "Customer2" (from myDB2)...

...could you give me any suggestions???

thx

How about doing an append query. If you look at an Append query, you can append data to a table in another database. Would that work?

hey kathryn!

...would be a step in the right direction...

but there should be a tool which manages that kind of stuff, shouldn't it?? ...I mean I have got about 50 tables and the relations are complicated. I would have to spend a lot of time finding out which data has to be appended first...(because of the referenc. integrity) -> the data should be consistent after the procedure...

This is a common problem, isn't it? ...

[sig][/sig]

Hi!

System for managing that kind of stuff, yes it does exist, and is a developing tool you by in addition to your Access license.

"Office Developer Edition" (ODE), which among other interesting things, contain a tool for creating and managing replicas, ie creating more than one physical database, which is perceived by the user as the same database. You can configure this to do data-syncronisation between the replicas at given intervalls, and when put up correctly, it will save you a lot of time.

If your merging is something you'll have to do often, you might want to consider this tool. It does not however solve your immediate challenge of merging the existing information.

Another interesting thing with ODE, is that you can distribute your Access-apps with "Access Run-Time-Engine", where the clients does not need to have Access installed to use the app.

Roy-Vidar [sig][/sig]

Replication is MS's answer if you want to do this on a regular basis, such as daily or more frequently, but it creates significant changes in the dbs, such as autonumber goes from sequential to random. If it's a one-time merge, if you do this as an append query and then work with the SQL view directly, it actually goes pretty quickly. You don't have to worry about indexes because Access updates them automatically. You don't have to worry about the relationships if they are the same in both db's. What you do have to worry about appending to parent tables before child tables, so that you don't violate referential integrity. [sig][/sig]