86,87,89,113; strange jump in table rows...

[Tezcatlipoca]

Hokay, so I have a very simple little database which has been written to make my job a little easier by logging the details of certain calls.
Everything on it seems to work fine, but there is one piece of strange behaviour that I just can't fathom...

I currently have 89 records stored (these were ported over from the old Excel table I used to use to log the calls). They imported just fine, and I can manipulate them as required. However, if I go to add a new record the next rwo to be added is 113 for some inexplicable reason. I can still add the record fine, and add subsequent ones as records 114,115,116, etc., but what I don't understand is why Access thinks the record number that comes after 89 is 113!

The table looks - to all intents and purposes - fine; just with its last rows going from 89 to 113 without listing 90-112 in between.

So...um...where are my missing records, and why does Access seem to think that 113 logically follows 89???

 

[Rich]

Compact the DB although Autonumbers aren't guaranteed to be sequential, if you need definite sequential numbers then you'll have to create them yourself.
Use the search function here there have been numerous posts and examples on the topic

 

[Tezcatlipoca]

Thanks, Rich. I have tried compacting the database, but it doesn't seem to clear the problem.

I don't absolutely need sequential numbers, these are only the numbers that Access assigns to each record that gets added; it's just irritating when searching records to have a jump from record number 89 to record number 113.

 

[Ron_dK]

It looks to me that records 90 to 112 were created earlier but may have been deleted.
Checl the table in which you create the records and see if there are any deletions.

 

[Tezcatlipoca]

It looks to me that records 90 to 112 were created earlier but may have been deleted.
Checl the table in which you create the records and see if there are any deletions.

Um...at the risk of sounding dense, how do I do that? I can check my table and it just lists the records 1 to 113, missing out 90-112, as stated earlier.

 

[Ron_dK]

You might look at a table 'paste errors". If this one is in your Dbase, appearantly some of the records imported were not inserted in the table.

 

[FoFa]

Basically what usually happens in this case, something told the autonumber to increment, so it did, but for some reason there is no record there. If you deleted some records (say 90-112) that would do it. If you importing and some rows failed to import, that could do it. If you imported a bunch of blank rows (like that NEVER happens from a spreadsheet) and deteled them, that would do it. Just a few examples. You have to think of the autonumber as a counter in the table. Once used, it gets updated to the next value. It DOES NOT go look at the current number and use the next one.

 

[Tezcatlipoca]

Thanks for the explanation, FoFa. I think this crept in whilst I was doing some testing on my AddRecords form (i.e. creating false records just to see if my coding worked).

I'm guessing the only way to reset that counter is to physically wipe my table and reimport the data then?

 

[boblarson]

Tezcatlipoca wrote:

I'm guessing the only way to reset that counter is to physically wipe my table and reimport the data then?

You don't want to go there. Follow Rich's suggestion above and if you want to see actual sequential numbers add them yourself. Because, once you start using the database, after importing, you WILL come up with holes as records are deleted. Plus, autonumber only guarantees a UNIQUE number, it doesn't guarantee that it will add them sequentially. There are instances where it does not add them sequentially - for example when using replication.

 

[FoFa]

As the others said, I do not count on an autonumber to supply a sequential number with no holes. If holes are acceptable, that's fine, but if no holes are acceptable, then create your own.

 

[gemma-the-husky]

Hide the autoid from your users, and just use it to manage links efficiently. Then if you want another column to serve as an identifier, you can, and you can amend it with impunity generally.

Autonumber should increment from the highest last number used (allowing for deleted items - eg if you start an edit and cancel it, you lose that number.) There is a reported compact database error that DOES NOT seed autonumbers correctly, and you ought to be aware of this - on MS knowledge base,sorry dont have the ref to end.