Can I edit AutoNumber Column in Access?

I've lost my data in Access base, and I've manage to bring them back 开发者_运维技巧but when I copy the values in the table with the AutoNumber Column it increments the numbers. Is there Any way to change it to int and then bring it back to AutoNumber?

Here is how I managed to do this in Access 2010:

1. Make a backup of your database. (Just to be safe.)
2. Right-click on the table in the tables list, and select Export->Excel. Accept all defaults.
3. Open the table in Excel and make the desired change to the autonumber field.
4. Open the table and delete all rows
5. Right-click on table in the tables list, and select Import->Excel
   • In the options, choose "Append to table" and select the table. Accept defaults for all other options

This might not be a viable solution for a large table. I don't think Excel can handle more than around 65K rows.

Don't copy the data with the user interface, but append it with a query. Because an Autonumber field is just a long integer with a special default value, you can append to it values that already exist. That doesn't work in the UI, but only in SQL.

An Autonumber field has a few other properties that are different from a normal Long Integer field, but in terms of appending data, those are not relevant. One of those properties is that it is not editable once it's populated, and another is that you can have only one in each table.

I've manage to insert the AutoNumber fields by code from c#. I take all the data I need and just inserted in an empty table.

How are you bringing the data back? It should be possible to append the data from your table and to keep the existing numbers.

It is necessary however, that you paste from an integer field to the autonumber field. You cannot change a field to autonumber from integer once there is data in the field, but you can change to integer from autonumber.

Make backup of your data table. Delete all data form original table and then do compact & repair your database. By doing this, auto number field will be reset at 1. You may now append your data from backup table.

SQL code like

   insert into <tablename> 
   (<column 1>, <column2>, ...) 
   values
   ( <value 1>, <value 2>, ...);

will do the trick if you include the autonumber column in your query. It's pretty tedious, but works. You can switch to SQL mode for any old query to enter this text (usually after preparing it in a text editor), or as @Dominic P points out, you can bring up a VBA immediate window and run DoCmd.RunSQL "INSERT INTO ..." which will give you a better editor experience within Access.