Importing data

[JodyAmy]

I'm using an import process to import Access 97 info into another Access 97 database. My problem is that the tables I am importing into have an autonumber field as the primary key. The source data is also an autonumber field, but some of the records have been deleted leaving the primary key (autonumber) field in this order: 1, 2, 9, 10.... The data now shows up w/ these primary keys: 1, 2, 3.... The problem is that I have related data in other info that use the old autonumber key field numbers. Now, my data appears to show the wrong information on forms and reports.

Is there any way to turn off/on the autonumber field in the new table until after I import the data or some other solution?? TIA!!!

Jody

 

[RickSpr]

If you use File>Get External Data>Import to import the table, it shouldn't change the autonumber field values. What method are you using? Rick Sprague

 

[JodyAmy]

I have modified an existing macro that first transfers the source table to a temp table and then I run an append query to append it to the new table. Not the most efficient route, so if you know of any other ways then I would be glad to hear them. I am not against using code instead of macros since I was planning on getting rid of the macros anyway. Would a straight transferdatabase to my new table work?

Jody

 

[ggreg]

What about importing the data to a temp table and then
use a update query

 

[JodyAmy]

ggreg,

I found this on Microsoft's msdn online help:

NOTE: An update query can append and update records only if the tables contain a unique index that does not have the AutoNumber data type

I was trying out your suggestion, but I was encountering a few problems so I checked it out and found the above info.

Thanks.

Jody

 

[Quehay]

When I'm importing tables from an existing db that has relations I do this:

1. Create a new "Old ID" field
2. Update the existing Autonum values into that field (it's now a Long datatype, not Autonum)
3. Create a "Old Join" field for child tables that
keeps the existing fkey
4. Allow Autonumber to create new index by not trying to import the original Autonum field. You can do a join on the original "Old ID" field (it should be indexed) and update a new blank join field in the child table with the value of the new AutoNum.

This way you can recreate old joins at anytime to confirm your new joins, while bringing new records into the existing Autonumber sequence.