Recovering from an AutoNumber Disaster

Several readers responded with more information on what causes this problem, and how to recover from it

In response to AW 9.16, with the Access Archon article “Recovering from an AutoNumber Disaster,” several readers responded with more information on what causes this problem, and how to recover from it.

Access MVP Tom Wickerath says:

One cause is not having the latest service pack for the JET database engine. Surprisingly, the failure can still occur if you have the latest service pack (SP-8), but the table in question was originally created using the version that was susceptible to this problem (I seem to recall that it is SP-4 or lower).

Steven Foust writes:

The issue you described sounds a *lot* like Microsoft’s KB article You may receive an error message when you try to insert a new record in a table that contains an Autonumber field in Access 2007 or in Access 2003.  Using the method described near the end of the article maintains the current autonumber scheme, but corrects the underlying problem (a messed-up seed value for the autonumber field).

Sub ResetAuto()

   Dim iMaxID As Long 

   Dim sqlFixID As String 

   iMaxID = DMax(““, ““) + 1 

   sqlFixID = “ALTER TABLE  ALTER COLUMN _

       COUNTER(” &  & “,1)” 

   DoCmd.RunSQL sqlFixID 

End Sub

Helen’s comment:  See the entire KB article – it provides different recovery techniques for different versions of Access.

Jean-Philippe George writes:

The first step is to make a backup, after all this database is corrupted and will perhaps crash definitively soon (or is it the hard disk …)

A simplest way to do it is to create a new database and import everything (data + structure). Access will handle all the dirty work of recreating relation between tables and as it’s a new clean database so we can hope that our datas will be safe.

Helen’s comment:  I tested this method with my sample AutoNumber Problem database, and it worked – the problem table created the next value in the sequence when adding a new record to the problem table.  This method has the virtue of simplicity (though you do have to redo your Startup settings).