autonumbering woes -- missing numbers caused by deleted records

[Guest]

I discovered some duplicate records in my database, so I deleted them. The
only problem is that each record is autonumbered, and now when I add a
record, it says I have 100, but there are really only 92 (due to the deleted
ones). Is there a way to force Access to create records with the numbers that
were removed?

Thanks so much!

 

[Douglas J Steele]

No, there isn't, nor is there any reason for Access to do that.

Autonumbers exist for one purpose: to provide a (practically guaranteed)
unique value that can be used as a primary key. The presence of a gap in the
number doesn't impact that purpose. In fact, it's actually usual not to show
the user the value of the Autonumber field. If you happen to use replication
to make copies of your data (so that users at multiple sites can update it),
you'll find that the Autonumbers are changed from sequential to random, with
no way to change back to sequential.

 

[Guest]

Using Recordset you can add records in the AutoNumber, and then edit the rest
of the records, or just isert them when you insert the auto number

Dim mydb As DAO.Database, myrec As DAO.Recordset
Set mydb = CurrentDb
Set myrec = mydb.OpenRecordset("Select * from TableName")
myrec.AddNew
myrec![AutoFieldName]= MissingNumber
myrec.Update

 

[Guest]

Ok, I understand the concept. But now I have a follow-up question. I was
(until I figured out I have missing record numbers!) using the record number
to determine the total number of records.... how can I do that now, if I
can't force Access to recreate those record numbers?

Thanks,
Patience

 

[Douglas J Steele]

One way is to use the DCount function on the table:

DCount("*", "MyTable")

In a form, you can move to the end of the RecordsetClone, move to the end
and check the RecordCount property:

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.MoveLast
MsgBox "There are " & rst.RecordCount & " records"

 

[Bill Edwards]

SELECT count(*) from TableName

Ok, I understand the concept. But now I have a follow-up question. I was
(until I figured out I have missing record numbers!) using the record
number
to determine the total number of records.... how can I do that now, if I
can't force Access to recreate those record numbers?

Thanks,
Patience

 

[dylan touati]

salut comment tu t'appelles moije m' ppelle dylan touati repon moi on fera
connaissanse