AutoNumber Field

[mura]

I have a table with 6000 records. The last autonumber is 8810 and now my next autonumber wants to start at 2752. Is there a way that I can renumber that entire field to number correctly? I have tried compacting and repairing the database. Your assistance is appreciated.

 

[The_Doc_Man]

Is there a way that I can renumber that entire field to number correctly?

But it IS numbered correctly.

You see, an autonumber field, because it is created by Access solely to keep record numbers unique, is ALWAYS correct.

What is NOT correct is to assign any meaning to the numbers. When you say "number correctly" you are assigning meaning. Don't do that.

If you wanted a number that had meaning, you should not have used an autonumber. Because an autonumber CANNOT be relied on to be continuously numbered, among other things.

 

[RoyVidar]

Here's one of the articles Pat Hartman refer to.

http://support.microsoft.com/?scid=kb;en-us;884185

It does work on my setup, as long as the autonumber field is not involved in relationships with other tables. Edit: Unless the reason for this to happen is something other then mentioned in the KB articles.

It seems Service Pack and compact repair might be one of causes for the bug. See for instance
http://support.microsoft.com/default.aspx?scid=kb;EN-US;257408

 

[rockman]

Pat,

I tried your code on the link, but ADOX is not in any of the libraries I have installed. Any suggestions? I'll try a kb article method in the mean time.

Thanks,
Jeff
Access 2003
XP Home

 

[rockman]

Got it working!

FYI -
I was able to reset the autonumber using a variation of the code provided by the kb link that RoyVidar posted (thanks Roy). The code provided by Microsoft didn't exactly work for me initially and therefore I'm going to publish the code that did work below.

Important Notes:
- All relationships have to be removed from the table - as Roy had pointed out. Some relationships may be hidden (I made this mistake initially) so click the "Show All Relationships" button on the relationships screen.
- Remove all indexes from the table (such as the Primary Key).
- RunSQL was erroring for me so I switched to the ADODB Open statement.
- Microsoft's version resets the next autonumber to the max autonumber such that when a new record was created it duplicated the last autonumber-- hence notice the lMax + 1 variation.

To run the code below type the following example in the immediate window:
Call ResetAutoNumber("tblEmployees","fldEmployeeID")

Public Sub ResetAutoNumber(sTableName As String, sFieldName As String)
Dim lMaxID As Long
Dim rs As ADODB.Recordset

  lMaxID = DMax(sFieldName, sTableName)
  Set rs = New ADODB.Recordset
  rs.Open "ALTER TABLE " & sTableName & " ALTER COLUMN " & sFieldName & " COUNTER(" & lMaxID + 1 & ",1)", _
          CurrentProject.Connection, adOpenKeyset, adLockOptimistic
  Set rs = Nothing
End Sub