Fixing ArcGIS personal geodatabase autonumber problem

A number of HBSMR clients have been experiencing problems with creating new features in ArcGIS personal geodatabases via MapLink, resulting in a error similar to this:

Your edits could not be saved
Error HRESULT E-FAIL has been returned from a call to a COM component.
Would you like to keep the temporary edit layers in the map?

The problem is due to a fault with the MS Access database that forms the geodatabase, which means after a feature has been moved or deleted the database tries to reuse that ID value and this upsets the autonumber process, or so we believe.

At present we do not have a solution to this problem but we do have a bit of code that can be used to reset the autonumber values for any chosen field. This code can be used as a function and bundled in an mdb with a macro that runs the function and sent out to clients as needed. The code for the functions is as follows (this was originally Hartmut's code but I modified from the a sub to a function adding the return and msgbox, just so I could see that something was happening):

 

Public Function ResetAutoNumber(ByVal tableName As String, ByVal autoNumberColumnName As String)
    Dim maxAutoNumber As Long
    maxAutoNumber = DMax(autoNumberColumnName, tableName) + 1
    DoCmd.RunSQL "ALTER TABLE " & tableName & " ALTER COLUMN " & autoNumberColumnName & " COUNTER(" & maxAutoNumber & ",1)"
    ResetAutoNumber = True
    MsgBox tableName + " done!"
End Function

 

And macro simple needs to have a number of RunCode lines that call this function for each of the layers in the personal geodatabase, like so:

 

RunCode

       Function Name ResetAutoNumber ("Mon_point", "OBJECTID")

RunCode

       Function Name ResetAutoNumber ("Mon_poly", "OBJECTID")

RunCode

       Function Name ResetAutoNumber ("Mon_line", "OBJECTID")

 

Comments

Find out more