Record auto numbering

[Hondo]

I suppose I shouldn't be concerned, but my record autonumbering jumped from
135 to 432,233 when I was trying to copy and paste common info. Any way to
reset? Access Rookie

 

[Jerry Whittle]

If the numbers don't have "meaning" nor need to be sequential, no problem.
Actually if they needed to be sequential or have meaing, there would be a
problem as autonumbers often aren't as you just found out.

Still that is quite a jump. Copying and pasting can be a problem. It's
better to import instead.

You could empty the table, like exporting to Excel, then do a compact and
repair. Next import the records back. But it really isn't worth the effort.

 

[jero]

Hello,
Something like :
Public Sub InitNumAuto(A_Table As String, NumAuto As String)
'Ex : InitNumAuto "Table1","Num"
Dim Rst As DAO.Recordset, SqlStr As String
On Error Resume Next
SqlStr = "SELECT * FROM " & A_Table
Set Rst = CurrentDb.OpenRecordset(SqlStr)
With Rst
' Delete all records
.MoveFirst
Do Until .EOF
.Edit
.Delete
.MoveNext
Loop
' Update field "AutoNumber"
.AddNew
.Fields(NumAuto) = 0 'To start at 1, 99 to start at 100, ...
.Update
' Delete first record
.MoveFirst
.Edit
.Delete
.Update
.Close
End With
Set Rst = Nothing
End Sub
Bye

 

[John W. Vinson]

I suppose I shouldn't be concerned, but my record autonumbering jumped from
135 to 432,233 when I was trying to copy and paste common info. Any way to
reset? Access Rookie

Don't bother.

Autonumbers have one purpose, and one purpose only: to provide a meaningless
unique identifier for a record. They'll always have gaps. They have a range up
to two billion odd (2^31 - 1) and will then pick up at negative two billion
odd and count up toward zero.

Copy and paste can introduce large gaps, as you have seen; for this and for
other reasons, it's often preferable to use Append queries or other techniques
to add new records. Where are you copying and pasting from?

For what it's worth, the only way to get back to gapless sequential numbers is
to create a new table, with an autonumber ID; and run an Append query
appending all the fields except for the ID. If your table is related to other
tables this can get complicated because you'll need to be sure that the
records link up.

 

[John W. Vinson]

Hello,
Something like :
Public Sub InitNumAuto(A_Table As String, NumAuto As String)
'Ex : InitNumAuto "Table1","Num"
Dim Rst As DAO.Recordset, SqlStr As String
On Error Resume Next
SqlStr = "SELECT * FROM " & A_Table
Set Rst = CurrentDb.OpenRecordset(SqlStr)
With Rst
' Delete all records
.MoveFirst
Do Until .EOF
.Edit
.Delete
.MoveNext
Loop
' Update field "AutoNumber"
.AddNew
.Fields(NumAuto) = 0 'To start at 1, 99 to start at 100, ...
.Update
' Delete first record
.MoveFirst
.Edit
.Delete
.Update
.Close
End With
Set Rst = Nothing
End Sub
By

Hondo, just note that Jero's code will a) completely destroy all the data
currently in your table and b) will fail since Autonumber fields are not
updateable.

 

[Jeff Boyce]

Why does this matter?

Who's looking at the Autonumbers?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[De Jager]

Why does this matter?

Who's looking at the Autonumbers?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.