Importing records from one table to another

[Rob]

Posted originally in Modulescoding forum by mistake.

Hi, Using Access 2000.

I have lost some records in table within a large database (mdb file),
however I'm lucky enough (I think) to have a back up of the whole database
from a week or so ago.

My problem is that I want to update the working database table with records
from the backup without overwriting the whole database. I would also only
like to add records from the backup to the original table where the records
don't exist (i.e. they weren't lost).

Do I do this with code or can I do via the Table view in the mdb?

Has anyone some sample code or instructions? There are only 8 fields, but
12,000 records.

Thanks, Rob

 

[Cheese_whiz]

Hi Rob,

*****NOTE: see note below before proceeding.

First thing is getting the backup data into your current database. If we
are talking tables here, I'd just link the table from the backup data
database in the main database (files>get external data>link tables). When
you are done with all this, and assuming you won't need to repeat it, just
delete the link to the backup data table.

After you have the data, it's just query work. Use the query wizard and the
'find unmatched records' query to find the records in the (linked) backup
data table that don't have matches in the 'main' table. Make sure to include
all the fields you want to include in the records you will be copying (most
likely, that means ALL the fields).

Then use THAT query (the unmatched records query you just made) in an append
query....appending it's records to your main table (the table that is missing
the records). Open up a new query in design view, show the query you just
made, change the query type to 'append' (there's a button/combo box in the
toolbar area that you can change the query type with), and drag down all the
fields to the grid....making sure that each field you drag down has the
correct match in the 'append to' row below it. Once you have done, just run
the query (either by hitting the 'run' button at the top in query design
view, or by double-clicking on the query in the database window.

Note: Two things:

1. ALWAYS backup your database before doing anything like this. If you are
paranoid like me, you'll back it up a couple of times.

2. If you have JUST lost data in that ONE table, the above process should
work fine. If you have related data in other tables that needs to be
restored, then I think it will be just a matter of repeating the entire
process using different tables. Think about it though. You might save work
by using that 'find unmatched records' query above to pick out the records
from other tables that need copying, but that will ONLY work BEFORE you run
the append query. Once you run the append query above, the 'find unmatched
records' query will return NO records.

Hope that helps,
CW

 

[Rob]

Thanks CW, I'll give it a go, but backing up twice.

Regards, Rob