Ms Access 2003 Bug – Crash after Compact/Repair & Autonumber Resetting

Two little tidbits of information today. Firstly, a bug that occurs in Access 2003 after you try run the Compact/Repair function. This is a really annoying bug that seems to occur randomly in Access 2003. What happens is that for one reason or another your database corrupts, and when you try open it, Access tells you it must be repaired, you hit OK, then Access tells you that an error occured (abliet with very little detail other) and must be close. You close, try reopen, and you run into the same stumbling block – the database must be recovered (also, assume in this scenario, the decompile switch has not worked).

There is a good description of how this bug occurs on this Google group page.

Unfortunately, in this situation, I couldn’t recover from it using Ms Access 2003 alone (i.e. even importing all the objects into a new blank database would not work) and I lost patience, and simply loaded up the 2003 database in Access 2007 which fixed the corruption issue in about 2 seconds flat. If anyone could enlighten me as to another way to fix it, please comment. I hear, and read, that this is an unconfirmed bug in Access 2003.

After recovering the system, I reloaded it in Access 2003 and it worked fine, but I noticed some records had gone ‘bad’ in one of the main tables, I promptly fixed them, but then realised that the autonumber ‘counter’ I used (not a primary key, in case you ask) was seeding about 30,000 numbers above the previous record, so I sighed and shook my head and moved to tackle this next problem which was not as straight forward as I imagined.

Turns out resetting a autonumber usually means you have to rebuild the entire table from scratch and then relink all the relationships, if it happened to be a relational table. Mine happened to be one of the main tables in the database, and I wasn’t looking forward to checking that all the relational integrity was upheld because of one pesky autonumber field. Then I stumbled on this function from microsoft which did the trick and reset my autonumber field back to the correct number.

Function ChangeSeed(strTbl As String, strCol As String, lngSeed As Long) As Boolean
‘You must pass the following variables to this function.
‘strTbl = Table containing autonumber field
‘strCol = Name of the autonumber field
‘lngSeed = Long integer value you want to use for next AutoNumber.

Dim cnn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim col As ADOX.Column

‘Set connection and catalog to current database.
Set cnn = CurrentProject.Connection
cat.ActiveConnection = cnn

Set col = cat.Tables(strTbl).Columns(strCol)

col.Properties(“Seed”) = lngSeed
cat.Tables(strTbl).Columns.Refresh
If col.Properties(“seed”) = lngSeed Then
ChangeSeed = True
Else
ChangeSeed = False
End If
Set col = Nothing
Set cat = Nothing
Set cnn = Nothing

End Function

As the MS link says, “make sure that the Microsoft ActiveX Data Objects 2.x and the Microsoft ADO Ext 2.x for DDL and Security Libraries check boxes are selected” otherwise the code won’t compile. Add that code to a vba module, then open up the debug window (ctrl-g) and type “debug.print ChangeSeed(“tableName”, “Autonumber Column name”, valueToStartSeed)” filling in the necessary arguments to run the function, and voila! Autonumber reset!