Access import w/ related tables

[gigemboy]

Say we have an Access database that was sent to three people. There are two tables with autonumbered ID's, parent-child (one-to-many) relationship with the second table. Is there an easy way to import all three dbs into an exact blank copy of their db? The problem is that since there are related tables, you don't know what the new id's are if you import the parent table first. Then it is a hassle to go in, join the new id, create a new autonumbered field, etc etc.

Right now I am changing the autonumbered fields to integer in each individual database and renumbering them through an update query (cascade) sequentially so that each record throughout all three are unique, then merging these all together.

Is there a way to insert parent-child records in one go without having to do all of this? Cascade the related table with the new ID it gets when inserting?

[gigemboy]

*bump*

[sparbag]

Hello,

What you're asking is using Access to build a data warehouse application to ensure data integrity of the overall data sources?

My first opinion would be to insure that the primary key field(s) for both table were relevant fields within the table. By this you may be required to use two or three fields to build a composite key for the tables.
This task can only be achieved if all of the separate databases have identical tables with identical data types.

Starting with the child tables from the first database load them into the data warehouse. Choose the second database and start loading the child table into the data warehouse only if the record does not exist. If the record exist in the database then discard it. Continue with this for the third database.

Now you can revert back to the first database and start loading the parent table data. You will need two recordsets and two connections for this to be achieved. 1 to manage the parent data and the second to locate the primary key value from the warehouse data and write that value as a foreign key to the parent table in the data warehouse, whilst also writing the parent record.

Things from here will start to become complex. In data warehousing 80% of all time is spent completing ETL's (extract, transform and loading) of separate database contents into a single unified data warehouse.

To ensure that data redundancy does not occur in the warehouse before you can write a record from the second or third database you will have to test whether the record already exists in the warehouse.

I am not going to expand further as I have no idea what data is being written to the warehouse nor any understanding of the source of the original data.

Kind regards

Steve