missing record numbers

L

line

Hi there,
Inherited a 2003 db with the records autonumbered 1-6500. I've noticed that
records 2998 to 3001 are missing. Can I insert the missing record numbers
safely and not create havoc in the db?

Thanks
 
J

John Spencer

Yes, you can do so. BUT, why bother - Autonumber fields are meant to generate
a unique number, they are not necessarily going to be sequential with no gaps.

Are you going to fix this everytime a gap develops? A gap will develop if you
start to enter a record and cancel the entry or if you delete a record.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
J

John W. Vinson

Hi there,
Inherited a 2003 db with the records autonumbered 1-6500. I've noticed that
records 2998 to 3001 are missing. Can I insert the missing record numbers
safely and not create havoc in the db?

Thanks
Click to expand...

Just to amplify on what John recommended... Autonumbers ARE NOT RECORD
NUMBERS.

They have a different meaning and different function. They're not designed to
be sequential and gapless; in fact they can become random (if you replicate
your database for example). In practice there will ALWAYS be gaps.

If you want sequential values, don't use autonumbers at all; use a Long
Integer field and maintain it yourself, manually or in code. You'll need to
come up with some policy for handling deleted records (it's fairly common in
accounting applications to forbid deletions altogether, instead flagging
records as cancelled).
 
K

Klatuu

Both John and John have given good advise.
If what you actually need is a sequential numbering system for your data,
there are better ways to do it.
Autonumbers really should never even be exposed to users.
Record numbers are meaningless in Access and most other relational
databases. Even the number you see in the navigation buttons on a form are
not consistent with a specific record. It only shows the relative postion of
the record in the current recordset.

As to sequential numbering, you can use a long integer field and each time
you add a record, use the DMax function to find the current highest number
and add 1 to it; however, even this is not perfect. If user 1 starts a new
record and user 2 starts a new record before user 1 saves the record, you
will have duplicate numbers. Any time a record is deleted, you will have a
gap unless you have a routine that spins through the entire table updating
the field, but then in a live environment if you have one user deleting a
record and another adding a record, it wll go completely crazy. Which brings
to mind what the boss wants :)

If you can describe what it is you want to do, perhaps we can help with a
way to do it.
 
Top