Access front-end, SQL Server back-end skipping autonumber IDs

Question

Question title is the crux of the problem. I have an Access 2007 (2003 format) front-end with a SQL Server 2008 Express back-end. The input form has a subform linked to another table. When adding a record in the main form, the PK field of the table (set to auto increment) is skipping about four IDs (I say about because sometimes it's three, sometimes five, sometimes 4).

To illustrate, if the last ID is 1234, the ID of the new record might be 1238.

I've stepped through the code, but haven't found anything that would indicate multiple saves or deletes. This problem manifests regardless of whether any records are added to the subform.

I realize this could be anything, but I'm hoping someone might have some insight or suggestions of avenues to investigate.

Answer 1

fire up sql profiler and observer the RPC:Completed and SqlStmt:Completed events to see what exactly is getting executed.

SQL Server doesn't just skip numbers for no reason. it looks like it's inserting something and rolling it back or inserting rows fail.

Answer 2

Check the Identity specification on the database to see what the Identity Increment is. It may be incrementing at an interval greater than 1, though that wouldn't explain your odd numbering. It's a good starting point.

Also, you could be having people start a record and then deleting it ala the transactions being rolled back and the incrementer being increased.

Answer 3

It could be that some INSERTs in the table are being done within a transaction and the transaction is then rolled back - this would use up IDs, leaving gaps.