Discussion Locked

Reset ID Autonumber counter field on Table in Access

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi,

I import data from a text file into an existing table. The text file has no column headings, so I delete all records from my existing table and populate it with data from the text file each tim. I need the ID autonumber field. However, the ID autonumber field numbers gets very big with each table erase and new import. Is there any way to reset this ID Autonumber field?
The field seems to rember the count from the last table before the delte. It will not even remove the ID field and resetinstaet it with ethe same name or different name. Please help. You may email me directly as well at cchetty@austin.rr.com and post the solution (if you have one) in this thread.

Thanks!

Chris.

5 replies

by desktroll

Compacting a database will reset autonumbers to the lowest unused value. You can also reseed the autonumber by doing an insert into the table with the lowest value minus 1 as the value appended to the autonumber field.

It seems to me that you are doing this the wrong way. If your process is manual automate it. If it is automated drop the table, import to new table with an import spec and then add an autonumber field with a sql or DAO.

by chris.chetty

DeskTroll,
Thanks for the reply. How do I add an autonumber field with a sql or DAO (What is DAO).
Thanks!
Regards,
Chris.

by jsmdatawork

This is a routine I use to do basically what you want to do.

Private Sub DeleteData()
Dim db As DAO.Database
Dim Rec As DAO.Recordset
Dim a As Long
Set db = CurrentDb()
'Put your table name here
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
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

This discussion is closed to new replies.