More solutions to Access AutoNumber problems.
I have received several more solutions to the AutoNumber problem discussed previously with the Access Archon article “Recovering from an AutoNumber Disaster.” With these solutions, and the ones in the last AW, you should be able to find some solution that works for you, if you encounter this problem.
Dan Goldman makes the following suggestion:
Run the following append query:
INSERT INTO tblContacts ( ContactID )
SELECT Max(ContactID)
FROM tblContacts
You will get a dialog saying that Access can’t add the record, and asking if you want to run the query anyway. Click “Yes”, and the table is repaired.
Brian Manix proposes a simple solution:
I think that you are doing too much to recover the autonumber of a table in the article ‘AW #9.16 – Recovering from an AutoNumber disaster’. I’ve found that when this occurs, I create a new table, NewTbl, using the structure only method. As a new table, it has an autonumber field and it is already seeded and incrementing correctly. Then I will create a append query using the old table, OldTbl, as a source. Append all fields to the new table. Although data entry will not be allowed to put data into this field, the append query is allowed to put data into the new table’s autonumber field since it is a query. Once it is created, NewTbl can be edited and extended as a normal table and the seed would be set at the last autonumber value that the query.
This is just a bit quicker than your version and it keeps the exact field values, if they are not in conflict.
I’ve found that not only is this a solution to autonum fields that need recovering, but also my solution (using an append query) allows a developer to SET certain numbers in an autonum field.
This may be needed for other uses – and it’s simple. For example:
1) to seed a higher number than the current autonum
2) to fill in missing autonums (certain rules apply)
3) to set autonums to a sequence number, as in 10,20,30,40,50
4) to add then re-associate the correct autonum for a single, accidentally deleted master record ( or primary key).
and on …
Of course there are data integrity and relational issues here and the developer that uses this method should be very knowledgeable about the ramifications of doing this. I’ve also found that when I needed to do this, I also needed to permanently document it, at least with a named query.
Ian Savell contributes this solution:
AutoNumber fields get broken most often by appending records to a linked table using an append query that specifies a value for the autonumber field. Knowing that helps avoid the problem in the first place.
Here’s a much easier way to fix the problem, as described in Microsoft documentation:
You can use the following function in a Microsoft Access database (.mdb) to programmatically reset the seed value of your AutoNumber field. You can add the function to a module, and then run it in the Debug window. Or, you can call the function from a command button or from a macro.
Note: For this code to run correctly, you must reference both the Microsoft ActiveX Data Objects 2.x and the Microsoft ADO Ext 2.x for DDL and Security Libraries (where 2.x is 2.1 or later.) To do so, click References on the Tools menu in the Visual Basic Editor. 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.
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
A version of this function described by another Access pundit goes the whole hog, automatically finding the AutoNumber column, looking up the current highest value in the column and setting the seed to the next higher value. That way you can automatically recover wasted record numbers after accidentally creating a new record (easily done in Access).
