Autonumber Macro


J

jhicsupt

I am not that great in Access. I found this code to reset the Autonumber
field in a table. However I am unsure of how to substitute my fields in the
code.

Table Name = IP-1
Autonumber field in Table = "Primary"

Can you tell me how to substitute the Table name and the autonumber field in
the below code?

Thanks in advance.

Private Sub DeleteData()
Dim db As DAO.Database
Dim Rec As DAO.Recordset
Dim a As Long
Set db = CurrentDb()
Set Rec = db.OpenRecordset("YourTable", dbOpenDynaset)
?Make sure there are records to delete
If Not Rec.EOF Then
Rec.MoveFirst
Do Until Rec.EOF
Rec.Delete
Rec.MoveNext
Loop
End If
?Set a to the number before the number you want to start with
?Set it to 0 if you want to start at 1
?This will start the count at 15
a = 14
Rec.AddNew
'Set your autonumber field to a
Rec![ID] = a
?Make sure you use data that matches Field(1)
Rec.Fields(1) = "a"
?Update it then delete it
Rec.Update
Rec.MoveFirst
Rec.Delete
Rec.Close
Set Rec = Nothing
End Sub
 
Ad

Advertisements

D

Dorian

Why do you need to do this?
To reset the autonumber, empty the table and do a compact and repair.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
J

jhicsupt

This is for a multi-user environment. Compacting and repairing will prohibit
users from using database while this is going on.

I need this so that the person that covers for me will not have to go into
Design mode.

Thanks so much.

Dorian said:
Why do you need to do this?
To reset the autonumber, empty the table and do a compact and repair.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


jhicsupt said:
I am not that great in Access. I found this code to reset the Autonumber
field in a table. However I am unsure of how to substitute my fields in the
code.

Table Name = IP-1
Autonumber field in Table = "Primary"

Can you tell me how to substitute the Table name and the autonumber field in
the below code?

Thanks in advance.

Private Sub DeleteData()
Dim db As DAO.Database
Dim Rec As DAO.Recordset
Dim a As Long
Set db = CurrentDb()
Set Rec = db.OpenRecordset("YourTable", dbOpenDynaset)
?Make sure there are records to delete
If Not Rec.EOF Then
Rec.MoveFirst
Do Until Rec.EOF
Rec.Delete
Rec.MoveNext
Loop
End If
?Set a to the number before the number you want to start with
?Set it to 0 if you want to start at 1
?This will start the count at 15
a = 14
Rec.AddNew
'Set your autonumber field to a
Rec![ID] = a
?Make sure you use data that matches Field(1)
Rec.Fields(1) = "a"
?Update it then delete it
Rec.Update
Rec.MoveFirst
Rec.Delete
Rec.Close
Set Rec = Nothing
End Sub
 
Ad

Advertisements

J

John W. Vinson

I found this code to reset the Autonumber
field in a table.

Why do you need to do so?

Autonumbers have one purpose and one purpose ONLY: to provide a guaranteed
unique key. If you're assigning other meaning to them, you're skating on thin
ice.

312271, 312272 and 312295 are *just* as good as unique identifiers for three
rows as are 1, 2 and 3.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top