Moving Multiple Related Records

[Tony]

Hi All,

I've got a problem that I hope can be solved by some function in Access but
am unsure as to where to start. I'm using Access 2000 and have a database
with three tables linked by autonumber primary keys. The problem is that I
inadvertently blew away some records and their related records - first table
cascades to second cascades to third. I have a good backup and all the
records, but it's been awhile since the gaffe, so I just can't restore from
the backup without overwriting new records.

I think I start by building a make-table query in the backup to combine the
three tables, import into a temp table in the production db, then split into
three related temp tables. Where I'm stuck is how to append the records
from the temp tables to the production tables while maintaining the
relationships.

Any help is much appreciated.

Thanks & Ciao,

Tony

 

[Joan Wild]

Hi Tony,

I've got a problem that I hope can be solved by some function in
Access but am unsure as to where to start. I'm using Access 2000 and
have a database with three tables linked by autonumber primary keys.
The problem is that I inadvertently blew away some records and their
related records - first table cascades to second cascades to third.
I have a good backup and all the records, but it's been awhile since
the gaffe, so I just can't restore from the backup without
overwriting new records.

I think I start by building a make-table query in the backup to
combine the three tables,

No, I wouldn't do this. If you want just the records involved, I'd create
three tables (actually you could link to the backup database and use queries
to get the records, but your approach may be safer/easier).

import into a temp table in the production

Import the three temp tables.

db, then split into three related temp tables. Where I'm stuck is
how to append the records from the temp tables to the production
tables while maintaining the relationships.

You can use an append query to append the records in - just include the
autonumber, and append it into the autonumber field in your production
database - you can do this, rather than letting it create new numbers.

You might want to double check that those autonumbers were not reused (which
could have happened if you weren't uptodate with Jet service packs.

As always, test on a copy of the production mdb.