The AutoNumber data type is one major advantage of using Access. You can let the computer keep track of things like the next customer number to be assigned whenever a new record is created. If you read my article Primary Keys and Foreign Keys in Access, you know how important it is to have unique key fields.

Another advantage of Access is the ability of the program to import data from Excel and other delimited data files. That way, if you’ve been using another program to keep track of your customers and invoices it is relatively easy to move that data into Access. Since tables are the way that Access stores data, you can even create a table and import data on the fly using the Get External Data Wizard.

While it is perfectly okay to import data into a table that has a predefined AutoNumber field, you cannot convert an existing field into an AutoNumber field type. This becomes a problem sometimes because the command to import data and structure from Excel does not allow you to set a field type of AutoNumber. Bummer!

Converting a field of Number datatype to an AutoNumber datatype

The good news is that this problem is relatively easy to get around. For the purposes of this exercise, I am going to work with an imaginary table called tblOffices. I have imported it from Excel, but now I need the Primary Key column to be an AutoNumber datatype (right now it is a Number datatype of Long Integer).

1. Right-click tblOffices in the Navigation Pane and choose Rename from the dropdown.
2. Change the name of the table to tblOffices_TBD and press Enter. (TBD stands for To Be Deleted.)
3. Right-click tblOffices_TBD in the Navigation Pane and choose Copy from the dropdown.
4. Right-click in the Navigation Pane and choose Paste from the dropdown.
5. Change the name to tblOffices.
6. Click the Structure Only button beneath Paste Options.
7. Click OK.
8. Right-click tblOffices in the Navigation Pane and choose Design View from the dropdown.
9. Change the Data Type for officeID to AutoNumber.
10. Press Ctrl + S to Save.
11. Press Ctrl + W to Close.
12. Right-click tblOffices_TBD and choose Copy.
13. Right-click tblOffices and choose Paste.
14. Change the Table Name to tblOffices.
15. Click the Append Data to Existing Table button beneath Paste Options.
16. Click OK.
17. Double-click tblOffices in the Navigation pane and confirm that the data is present.
18. Right-click the tblOffices tab and choose Design View.
19. Confirm that the Data Type for officeID is AutoNumber.
20. Click tblOffices_TBD in the Navigation Pane.
21. Press the Delete key on your keyboard.
22. Click Yes in the warning dialog box.