Use Append Query for Identity Insert or to Set Initial Value of AutoNumber Field

After a lot of struggle with a database that I am developing, I finally stumbled upon a better solution to my woes. I knew there is something called Indentity insert in MySQL but just could figure out how to do the same in Microsoft Access.

Now I just need to test this out and see if it will help me to resolve lots of issues that I am having in one of my projects where database are failing to synchronize using Jet replication.

Fix:

By design, Microsoft Access always numbers AutoNumber fields beginning with the number 1. You cannot edit an AutoNumber field or change its starting value. 

However, you can force Microsoft Access to number an AutoNumber field with a number you choose by following these general steps: 

• Copy the design of the original table in which you want to set the starting AutoNumber value to a new table.
• Change the AutoNumber field in the new table to a Number field with a FieldSize property of Long Integer.
• Add a record to the new table, and set the Number field to a value that is one less than the starting number you want for your original table. For example, if you want the AutoNumber field to start at 100, type 99 in the Number field of the new table.
• Use an append query to add this new record to your original table. This action forces Microsoft Access to number any new AutoNumber fields with your number plus 1.

NOTE: Do not compact the database before you add a new record to the original table. If you do, Microsoft Access will reset the AutoNumber field value to the number 1.


Check out http://support.microsoft.com/kb/94821/ for the full reference.