DataBase - Autonumber restarting after compact and repair

Asked By Boon

08-Feb-10 05:07 PM

Hello,

Here is my situation.

I have table A that has the field ID as the first field. The field ID is the
AutoNumber and it is a primary key. Table A also has several fields in the
table (name, date,shipping date......)

The table A has  2000 records. Today, I worked in table A and removed some
records....  Then I compact and repaired the database. Now when adding new
data into the table A, the field ID did not start at 2001. It started at (I
think) the first missing number in the sequence. Also when I append the new
data to the table A, all the weird things happen. For instance, says I have
3 new records, and the I have 3 consecutive numbers in the sequence that are
still available as a key in field ID, I can append all 3 records. But say if
I have 3 records and I have only 2 consecutive numbers in a sequence, Access
appends just 2 records. I know this is confusing. I am still confused...

How can I work around this? I want to use AutoNumber field, but at the same
time, I want to be able to compact and repair and want the autonumber starts
at the highest number in the field ID..

thanks,
Boon

08-Feb-10 07:10 PM

Access Autonumbers are designed to serve as unique record/row identifiers
... nothing else.

They are unfit for human consumption, so if have an autonumber = "2001"
means something to you, it is time for a redesign!

Autonumbers start over at the next highest value after compact/repair.
Autonumbers "skip" values if you start a record and decide not.

Do a search on-line for "custom autonumbers" to find ways to roll your own
sequence number routine.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

09-Feb-10 10:16 AM

Thanks Jeff. Looks like I need to redesign my database to suit my need.

One question here...  Says I have a consecutive sequence of Autonumber from
1 to 100 in my table. Then I  deleted the records with autonumber= 10 and
50.

Question
1. without compact/repair, I notice that the Autonumber continue at 101,
102,,.....
2. If I do the compact/repair, what number will the Autonumber continue at?
10, or 50, or 101?

thanks!
Boon

09-Feb-10 11:03 AM

The next highest number (not the ones "in between").

This is as it should be ... imagine if you used that autonumber as an ID in
a parent table, and a corresponding foreign key in a child table.  Add a new
child record attached to Autonumber ID=50.  Now delete the parent table
record ID=50.

You have an orphan!

Worse yet, if you re-use "50", you just gave that orphan a new "parent",
with no guarantee they are actually related!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

09-Feb-10 11:28 AM

101 but you should not really care as long as it is unique and the proper
relationships and integrity is enforced.

--
Duane Hookom
Microsoft Access MVP