Maintain Autonumber Link when exporting two tables to another Access database

[Seamus59]

I have two tables, linked by an AutoNumber field in a one to many relationship. I would like to move records into another "Archive" database based on Year and retain the link. When I do my Make Table Query, the AutoNumber in the Parent database is renumbered from 1. I'd like to keep the original number to maintain the relationship.



I looked for posts relating to this, and thought I'd found it, but the post seems to have been removed. I know I can use an Append Query to do it, but I would like to have separate archived databases grouped by Year

Does anyone have the answer?

I quess I could do a make table with no data then "insert into", what do you think?

Exporting tables containing autonumber keys
www.accessforums.net
www.accessforums.net/...export.../2235-exporting-tables-containing- autonumber-keys.html

Exporting tables containing autonumber keys I have an Access 2002 database containing 2 tables (customer and transactions) linked by Autonumber (random) key fields. I have been asked ...

[Seamus59]

I used "TransferDatabase" to copy the structure and use an append query:

INSERT INTO destinationTBL (ID,...) IN destinationDBF SELECT VAL(ID),...;

There may be a more elegant solution, but I don't know it...Thanks anyway

[ssanfu]

I would like to move records into another "Archive" database based on Year and retain the link.

I am guessing you mean the relationship between the tables in the "new" database? Not between a table in the original database and a table the "new" database.


An alternative that I have used:

Lets assume you have a database with data for 5 years: 2009 - 2013 and that you have a field that has a date you can use to select records by year.
Make 4 copies of the current database. Rename the dB by adding the year.

In the 2009 dB, add cascading deletes for the main table link if not already enabled.
Execute a delete query to delete all records in the "One" table where the year is not 2009.
Remove cascading deletes if you added it.
Be sure to "Compact and Repair..."


Do the same for the the other 3 databases, changing the year.

The original database has all of the records, unless you elect to delete those that are pre-2013.

--------
Having said all that, I usually use a archive flag to indicate when a record should not be generally available. A check box or a text field that indicates the record is "archived". A little more work designing queries, forms and reports, but ALL of the data - historical and current - is in one database.

Unless the database (BE) is running up against the 2 GB file size limit, I prefer to have a flag and one database (BE) with all of the data.

[Dal Jeanis]

Really, you just want to make sure that the key in the archive table is not autonumber. It can still be indexed, but it should just be a "number" type field, and there wont be a renumbering issue.