Reset AutoNumber

  • Thread starter Thread starter Hank
  • Start date Start date
H

Hank

I'm creating a new database from an existing database and
copying/pasting fields from one to the other. The old datbase has a
table with approximately 15,000 records that starts at 2. I copied all
the records over to the new database and of course, the autonumbering
started at 1. I tried KB article ACC2000 but it's not working exactly
right since I need to start the autonumbering at 2. However, there is a
record at 1 that I need to keep as well.

Does this make sense? Any way to accomplish what I'm trying to do?

Thanks
 
Create an append query.
1. Open the QBE grid and create a select query that selects all the fields you need from the original table, INCLUDING the old autonumber.
2. Change the query type to Append.
3. Make sure all the fields map properly. If the column names are diferent in the old and new tables, you'll need to map them one-by-one. If they are identical, Access will map them automatically. Make sure that the old autonumber key maps to the new autonumber key.
4. Run the query. The records will keep their original autonumber id's.
 
Create an append query that selects the data you need from the original table and appends it to the new table. Make sure that the old autonumber key field maps to the new autonumber key fields.
1. Create a select query that selects all the columns you need from the original table.
2. Change the query type to append.
3. Choose the new table.
4. Access will automatically map any fields whose name is the same in both tables. If you have changed any names, you will need to manually map field by field, the ones with changed names. Make sure that the old autonumber key field maps to the new autonumber key field.
5. Run the query. The old data will be appended to the new table and will retain its original autonumber values.
 
Pat said:
Create an append query that selects the data you need from the original table and appends it to the new table.
Click to expand...

You'll have to excuse my ignorance for a moment. Should I follow the
steps in the KB article and *then* apply your recommendations? Or are
you suggesting I follow your suggestion instead?

Thanks again.
 
Pat said:
You didn't post the number of the kb article that you tried so I'm not sure of what steps it told you to follow.
Click to expand...

The article number is: KB article ACC2000
I gave you the steps to import your existing data and retain the original autonumber value for each row. Of course this presumes that the table that you append to is empty. If you are appending to an already populated table, any rows from the append query with keys that duplicate existing keys will of course be rejected.
Click to expand...


Thanks for your help Pat!
 
Back
Top