Problems with Autonumber after Import (1 Viewer)

winkmich

Registered User.
Local time
Today, 11:12
Joined
Jun 21, 2002
Messages
18
Hello there,

the problem I face is the following.

I had to change the structure of a table which contained the contact data of people. Every record resp. person was given an Contact-ID by Autonumber. I had to create some queries to change the table, then copied the query into Excel and imported the whole thing back into Access as a table. (maybe a bit too laborious :)

The table is now there again with the wanted changes, but due to the export/import etc. the Autonumber-function for the Contact-ID field is gone. And of course, it can't be changed afterwards!

Can anyone tell me how to avoid losing the autonumber OR
if not possible to get a new autonumber which is the same as the old autonumber (means Contact-ID).

So far, I have to include a new Contact-ID next to the old Contact-ID.

Thanks in advance, Mike.
 

David R

I know a few things...
Local time
Today, 06:12
Joined
Oct 23, 2001
Messages
2,633
Do you still have the old table?

You cannot 'change' a field after there's data in the table into an Autonumber, you're right. But you can use an Append Query to sneak around this restriction. The problem is, it will probably require you to redo your table.

We're going to be talking about three tables: Old Table (the one you used before), Main Table (your current one you're having problems with), and a Copy Table (see below)

First, make a copy of your table, if you've got any records in there that you've entered since this changeover (it sounds like you have). Then empty out the Main Table and Compact your database. This will reset your Autonumber to 0.

Now instead of using Excel to move the data from your Old Table, use an Append Query. You should be able to Append autonumber values in, in addition to any other fields that still belong in your new main table. Make sure you've got the right number of records compared to the Old Table.

Append Queries have to be 'Run' instead of just viewed (though you can view them too). Use the [!] button in the toolbar.

Finally, you'll have to run an Append Query to get your additional records from Copy Table back into the revised Main Table. That shouldn't be too difficult for you by now. :D

Make sure you backup and check every step of the way, but this should work, and because it's an all-Access solution your Autonumber field should remain intact.

Post back if you have further problems.
 

Users who are viewing this thread

Top Bottom