AutoNumber field in table: losing track of 'last number used'.

[Kev_EoE]

MS Access 2010.

I had created a table which included an AutoNumber field, properties "Indexed (No Duplicates)" although not a Key Field as I have three fields set as Key (all indexed/duplicates OK). The database was on a shared network, and accessed by two different people

Had been working fine for some weeks, and I have a habit of using the 'Repair/Compact' tool at regular intervals. There were some 18,000 records on it, with a similar table in another database that was used for training purposes, originally with a similar number of records but reduced to some 12,000 following a clear-out of older records. This Training system was also working fine for some time.

Following problems with entering new records - on both the 'live' and Training systems - I discovered that the internal 'index' that records the last AutoNumber used and hence allocates the next number for a new record had 'lost its memory' and was re-allocating serial numbers already used.

Searching on the Web I discovered that this was not unknown, and downloaded the appropriate code to interrogate the database and reset this 'last used' number. It seemed to work, until the database was closed again and reopened, at which stage it had gone back to an old already-used number.



Therefore created a brand-new database, manually created a duplicate table and imported the old data into it from the existing database, letting the new database and its new table allocate the AutoNumber (the old system's 'AutoNumber' field data was put into a new field). This seemed to work fine, and in testing I could close the database several times and re-open, with the 'last used index number' still being retained, until I ran the 'Repair/Compact' tool again, at which stage the 'last used number' was again lost, and subsequent new records being allocated an already-used ID.

Any thoughts?

Thanks.

[June7]

If you aren't using the autonumber in a pk/fk relationship, why bother having the field at all? It can be useful in some advanced nested queries but until that need arises, ignore the field.

I have never known C&R to cause this issue. Perhaps reinstall Access?

[JoeM]

I had come across some interesting issue with Autonumber some years back, when a Access MVP named Bob Larson set me straight. Basically, you should also not be using autonumber for anything meaningful, other than to just have a unique number. Note Bob's reply here, especially this line:

Autonumbers are guaranteed to give you a unique number but they are not guaranteed to be consecutive nor even positive.

Also note that using Compact and Repair erases all that old memory. Here is a little example you can work through to see this.

Example 1
Enter a bunch of data in a table that has an Autonumber field.
Delete all the data
Enter some new data
Most of the time, the Autonumber field will pick up where you left off

Example 2
Enter a bunch of data in a table that has an Autonumber field.
Delete all the data
Do a Compact and Repair
Enter some new data
Most of the time, the Autonumber field will start off at 1 again

That is because when you delete data from Access tables, it is marked for deletion but really isn't deleted from the underlying database, even though you cannot see it (I am assuming this is how Access usually knows where you left off). However, doing a Compact and Repair purges those deleted records from your database.

Bottom line is this: If you want a meaningful ID field (something more than just a unique number), you should not Autonumber. The only thing Autonumber can guarantee is uniqueness. You cannot count on using it to sort records, or to determine how many records there are by looking at the highest numbered autonumber field.

[eki einstein]

i think your problem in your table structure ..., using autonumber as an unique ID need a great understanding ms acces, in value skill 1 to 10, only people have value 8 and up can using autonumber as an unique ID in great way, backday i use some text filed as an unique ID, but it also bring more limitation in using ms acces .....

Compact & Repair data base do just like this :

ID : Name :
----------
1 : Jhon :
2 : ryan :
3 : susie :
4 : wyat :
5 : Luke : <- if you deleted these field
6 : willy : <-if you deleted these field

with no compact and repair

ID : Name :
----------
1 : Jhon :
2 : ryan :
3 : susie :
4 : wyat :
7 : New :

with compact and repair

ID : Name :
----------
1 : Jhon :
2 : ryan :
3 : susie :
4 : wyat :
5 : New :

i think what ms office 365 do with their limitation in ms acces online "True" about "lets bring limitation to table" and do more in "Query or form" ....., because it bring more stable database then you dont need "compact & Repair" "C&R" good tools but also bring new problem when u using in "real live" Ms acces app....,
I only use "C&R" when i finish build and tested an ms acces app, to deliver to customer it must have First value again, so i clean the database do "C&R" then packing -> deliver to customer.... dont use in Live ms acces app

I think it more need then word to solve your problem, it need your database at least attach the picture of the relationship that you make so it can be more easy to solve

[June7]

I do avoid autonumber whenever I can but it does have it's uses.

I just did some testing of autonumber and the impact Compact & Repair has on it.

I entered and deleted records, 44 records remained in table and were autonumber 1 to 44, ran C&R. Added new record and the autonumber started with the next available number (45). I also tested deleting record (42) so there was a gap in the autonumber sequence, ran C&R. The gap remained in the sequence and next record was created with the next available number (46). There was no duplication of numbers.

[eki einstein]

what basic table do you run... the "offline table" or "web table"? if "web table" offcourse the C&R wont work ..., its work in offline table only

[June7]

Normal offline database. I've never built web database.

[eki einstein]

sorry june what are u writing its true their a basic compact and repair for "the number gap" sample 1,2,3,deleted,5,deleted,deleted the next number autonumber is 6 after you compact and repair not 4, at least it still unique no duplicate so what im saying we need to see the database to understanding "whats the realy problem is !
i still think the structur of the table that Kev_EoE made, are the main problem

[Kev_EoE]

Hi, thanks for all your replies.

I need to use the AutoNumber field in this particular table as it links through to a number of other tables, for which using the three PK fields would be impractical, even by concatenating them this would create a text field of 20 digits, not very quick or efficient as an indexed PK/link field.

Have also carried out some further testing work. Using a small number of records (say 10-20) the C/R does not cause a problem (and indeed never has for me in some 18 years of using Access, albeit with AutoNumber tables probably only having a few hundred records at most), and any records deleted prior to the C/R also do not have their AutoNumber re-used, so everything fine at this stage.

The problem encountered seems to only appear when there are a 'large number' of records in the table in question, certainly appeared when 12,000+ were in, although what the trigger point is I do not not know. A number of AutoNumbers have not been allocated during using the d/b, leaving quite a few block gaps. This may be down to attempting to enter records that cause duplicate records that contravene the Key Field rules and hence do not get Saved (most of the data entry on a day-to-day basis is by copying and pasting in groups of perhaps a dozen or so records; the original data populating the table was historic and imported directly in from an Excel file). However, even where the odd record has been deleted anyway, running the C/R does not change already-allocated AutoNumbers. What running the C/R does do, however, for the large table, is reset the 'seed ID' (system record of 'last number used' in table's AutoNumber field) to a lower number than that already in use. This can be reset by running some code in an Access Module that 'inspects' and compares the 'seed' to the last-used AutoNumber and corrects the seed figure, and this then seems to work OK until and unless the d/b C/R routine is run again.

The work-around that I am therefore now using is to (a) retain the tables in a separate Access d/b, linked through to the 'front end' d/b, and only C/R the 'front end', which leaves the tables and hence their seed figure untouched. If I need to C/R the tables' d/b I then run the Module (code) to reset the seed number, and everything then should be back to normal.

A most unsatisfactory position, however, that Microsoft should look to sort out in their next Service Pack.