Autonumber sudden jump 3

[lacasa]

I have a two tables with a one to one relationship on an ID field. The ID field in the first table is an autonumber field and primary key. The second table has an ID which has a long integer data type. I made two tables one to one simply because the first table was getting too big.

Anyway the autonumber in the first table goes from 1 to 26, then it jumps to 1572878 and continues counting up from there. Forms that I had created stop at 26 on the record navigation. New forms created will include all records.

Why the jump? Can I bring the numbers back in line easily. I know it is only a unique number and should not matter, but it bugs me to have a gap of over one million. Can I correct the old forms? I have already tried compacting and repairing.

 

[THWatson]

lacasa
Here is a link to Microsoft that shows how to reset an AutoNumber field.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;812718

Tom

 

[willir]

lacasa

The use of Autonumber is to uniquely identify a record. One of the conditions for a good database and as part of "normalization" is that each record be uniquely identified.

The purpose of Autonumber is not to "count" records, or give an indication of the order records are created. The fact that the default setting for autonumber is to increment serially may be convenient, but should be treated as "cosmetic".

I can go on about this, but just consider these two points... a) Start creating a record, not the AutoNumber assigned (your ID number), and then cancel out without saving the record. Then create a new, and not that the next autonumber is now one higher. This is the way the system is designed. b) Imagine that you have to delete a lot of records. Say ID numbers 5 to 255 out of 100,000 records. Do want the "system" to now renumber all the other records from 256 to 100,000 to maintain the sequential sequence ... and renumber and related tables that use the ID as a foreign key?? The answer is NO.

So please don't be concerned about missing Autonumbers.

1 to 26, then it jumps to 1572878 ... Why the jump?

Very Good question. The next "Autonumber" is kept in one of the hidden system tables. You can get a large jump like this when developing a database, or running a large import and then, in either case, deleting the records. To jump by other 1.5 million is pretty wierd, and I suspect you did not delete that many records.

Another possibility is the randomization feature. By default, an Autnumber will use a sequential / incremental. Open your table in design mode and select the autonumbered field. Look at the bottom of the form at the "Field properties". Click on the field for "New values". From the drop down menu, you will see the other option is "random". Don't change the value from incremental, just yet.

A long interger (default value for AutoNumber) uses 4 bytes to accommodate a range of numbers from -2,147,483,648 to 2,147,483,647. Do you see a problem here? When using incremental or sequential numbers, you are loosing half of the available numbers as a long interger, specifically the negative numbers. However, since a long interger can go as high as 2.1 x 10^8 (2.1 billion for some, 2,100 million for others), the loss of half the available numbers is moot since a database of this size should be using Access.

Once you change a database from incremental -> random, you can not easily go back. However, I suspect, as long as none of the numbers are negative, I suspect the same method for resetting the Autonumber may be used for changing a random Autonumber fied -> incremental Autonumber field.

I suspect something like this may have happend to your database.

Remember when I said don't change the Autonumber from incremental to random at that time? I actually use the "random" number when using Autonumber - alot. But it will generate huge numbers, positive and negative. I am okay with this, and to avoid confusion on the end-user end, I hide the text box used to capture the Autonumber. Before doing anything like this though, back up your table.

Richard

 

[lacasa]

Thanks for the info. Just to let you know the users of this database probably did delete a few - less than 10 records, but nothing like 1.5 million. It is possible that one of the users changed the field inadvertly to random, it picked out a new number, and then switched back to incremental?

Of course that is my next problem or challenge with this database - adding security to stop someone from messing it up.

My existing forms - is there a way to refresh them? They still only show 26 records on the navigation?

Thanks for your help.

 

[willir]

It is possible that one of the users changed the field inadvertly to random, it picked out a new number, and then switched back to incremental?

Weird, but I suspect that this may have happened.

Of course that is my next problem or challenge with this database - adding security to stop someone from messing it up.

A good start is to split the database into the "front end" with the forms, reports, queries and static data, and the "back end" with the tables, relationships and schema design. The BE is stored on a server (and routinely backed up) and the FE can bestored on the workstation, or on a server. This is another step, and there are a few tricks, but by splitting the database, the user would be exposed to FE and can not directly modify the design.

But security and splitting a database are topics for another thread.

My existing forms - is there a way to refresh them? They still only show 26 records on the navigation?

The 26 records indicate that is the number or records for the form (and table?). The only thing that may interfere is if you have a filter on the form that is preventing you from seeing "all" the records. To verify, create a query using the Query Builder.

By "refreshing", do mean, restart the count? Since you have 26 records, probably the easiest way would be to copy the records "into" another table. I am not sure about your design, but one way might be to create an indentical table, and then use an "Append" query. Again, I don't know your design, but you probably don't need to copy the AutoNumber field over. The AutoNumber field in the new table will generate a new Autonumber. The precaution here is if the Autonumber is used to link data in a related table.

Some further reading on design...
Fundamentals of Relational Database Design by Paul Litwin
Download document
Read on-line (HTML)

Micro$oft's answer...
Where to find information about designing a database in Microsoft Access
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

Thanks for the star.

Richard

 

[r937]

richard, very nice explanation, must've taken quite a while to write that up, and it's worth another star

p.s. thanks also for the link


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[willir]

Thanks lacasa and thank you Rudy. with all your experience and "stars" to your credit, it means a lot to me for your kind comments.

 

[lacasa]

Thanks again.

It turns out my form was based on a query with two one-to-one tables. On table had only 26 records - the other one had 36. Because of the join properties were incorrect the form only showed 26.

Anyway I have a lot to learn. You have been very helpful. Thanks for the links.