Autonumber Key Field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've imported some customer data from an external source, and the last record
has a CustID of 1198. I would like the next record to AutoNumber to 1199
(and continue on sequentially), but Access won't let me now change the data
type to AutoNumber.

Any ideas?
 
You can create a new table with an AutoNumber field, and import all the
existing records.

1. On the Tables tab of the Database window, select your table.
Copy (Ctrl+C.)
Paste (Ctrl+V.)
Tell Access you want Structure only, and supply the new name.

2. Open this new table in design view.
Delete the Number field.
Add an AutoNumber field instead.
Make the AutoNumber as primary key.
Save.
Close.

3. Create a new query, using the original table.
Change it to an Append query (Append on query menu.)
Answer that you want the records appended to the new table.
Drag all fields into the grid.
Check they are all assigned to the right fields (including the AutoNumber.)
Run the query.

4. After checking the results, delete any relations your old table has
(Relationships on Tools menu).
Then delete the table.
Compact: Tools | Database Utilities | Compact.
Rename the new table with the old name if desired.
Recreate the relationships.

Before you reuse the same name for the table, make sure the Name AutoCorrect
boxes are unchecked under:
Tools | Options | General
Explanation of why:
http://allenbrowne.com/bug-03.html
 
I've imported some customer data from an external source, and the last record
has a CustID of 1198. I would like the next record to AutoNumber to 1199
(and continue on sequentially), but Access won't let me now change the data
type to AutoNumber.
Click to expand...

Allen has shown you how to do this, but be aware that Autonumber fields are not guaranteed to be sequential. If you add
a new record and type anything in the record, then decide not to save it, Access will NOT reuse the number just
discarded. If your app calls for strict sequential numbering, you'll have to do it yourself.
Any ideas?
Click to expand...

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com
 
Back
Top