Yup!OK, found the code back in 2012
Code:Function GetSeedADOX(strTable As String, Optional ByRef strCol As String) As Long 'Purpose: Read the Seed of the AutoNumber of a table. 'Arguments: strTable the table to examine. ' strCol = the name of the field. If omited, the code finds it. 'Return: The seed value. Dim cat As New ADOX.Catalog 'Root object of ADOX. Dim tbl As ADOX.Table 'Each Table in Tables. Dim col As ADOX.Column 'Each Column in the Table. 'Point the catalog to the current project's connection. Set cat.ActiveConnection = CurrentProject.Connection Set tbl = cat.Tables(strTable) 'Loop through the columns to find the AutoNumber. For Each col In tbl.Columns If col.Properties("Autoincrement") Then strCol = "[" & col.Name & "]" GetSeedADOX = col.Properties("Seed") Exit For 'There can be only one AutoNum. End If Next 'Clean up Set col = Nothing Set tbl = Nothing Set cat = Nothing End Function Function ResetSeed(strTable As String) As String 'Purpose: Reset the Seed of the AutoNumber, using ADOX. Dim strAutoNum As String 'Name of the autonumber column. Dim lngSeed As Long 'Current value of the Seed. Dim lngNext As Long 'Next unused value. Dim strSql As String Dim strResult As String lngSeed = GetSeedADOX(strTable, strAutoNum) If strAutoNum = vbNullString Then strResult = "AutoNumber not found." Else lngNext = Nz(DMax(strAutoNum, strTable), 0) + 1 If lngSeed = lngNext Then strResult = strAutoNum & " already correctly set to " & lngSeed & "." Else Debug.Print lngNext, lngSeed strSql = "ALTER TABLE [" & strTable & "] ALTER COLUMN [" & strAutoNum & "] COUNTER(" & lngNext & ", 1);" Debug.Print strSql CurrentProject.Connection.Execute strSql strResult = strAutoNum & " reset from " & lngSeed & " to " & lngNext End If End If ResetSeed = strResult End Function
It has been working flawlessly (until I split the database). As I said above, simply converting that temp table back to a local table fixed the problem it was having.
Last edited: