Auto Number

[bladeuk]

Hi,

can anyone advise?

I have a database (Access 2003) which has an input table that gives an auto number to each new entry created.

For some reason, after record 3449 the auto number has decided to populate itself to 164237057 and subsequent records are now following from that number. e.g. 164237058, 164237059 and so on.

I have other databases I have built that have far more records in than this but this has not happenned before.

Can anyone shed any light?

Appreciated

 

[apr pillai]

Create a new Autonumber Field in the same table. If the numbers are appearing correctly then delete the corrupted field.

 

[bladeuk]

""Microsoft Office Access allows only one AutoNumber field per table""

??

 

[jackd]

Autonumber just means unique, it does not mean contiguous - necessarily.
You should not have to know what the value of an autonumber field is.

If you really are concerned, you may wish to read Allen Browne's approach here
http://allenbrowne.com/ser-40.html

Just my 2 cents....

 

[bladeuk]

Hi, thanks for this.

I understand that the autonumber may not be continuous but I have built dozens of these simple databases and this is the first time I have ever had this issue.

Going from 3449 to 164237057 just really threw me.

I have also noticed I cannot alter this reference number in the table (probably a good thing)

I'm not that great with coding as well, so where would I put the code suggested in Allen Brownes article(s) ?

Thanks for the help.

 

[SydneyGeek]

You can rest Autonumber fields by deleting records, then doing a Compact and Repair (which rebuilds the indexes).

If you don't already have child records for the ones with huge IDs, try:

Select those records and use a Make-table query to push them to a new table.
Delete them from the original table, then Compact & Repair.
Use an Append query to push the saved records back into the main table. Don't append the ID; let the DB create the new IDs for you.

Denis

 

[james_lankford]

is the data type for the autonumber still long integer ?

I had someone take a db once and put it on their laptop so they could work on it at home while others worked on it at work

then when they brought their laptop in and tried to synchronize the database, access changed the autonumber datatype to GUID and they became really big numbers like you describe

 

[SydneyGeek]

Hmmm... forget about that possibility.

... or Long Integer, but with the increment set to Random instead of Sequential?

Denis

 

[bladeuk]

Hi Denis, James,

Auto number field is still Long Integer and new values is set to Increment.

Tried doing the deletion of the records and the compact and repair. When I went back in for a new record, which is input via a new record form, it still came up as the really big number.

Dave

 

[SydneyGeek]

I'm not sure if this will help but give it a try...

http://www.helenfeddema.com/access.htm

Download article 165; Recovering from an AutoNumber disaster.

Denis