473,686 Members | 2,223 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Restart Autonumber

If I have a table with an autonumber primary key and 100 records and I delete
the last 50 records, the next record added would have a primary key of 101. Is
there any way to have the primary key start at 51 after the last 50 records are
deleted?

Thanks!

Traci
Nov 13 '05 #1
35 7261
That's the way autonumbers are. In fact, you're lucky that the numbers
currently correspond to the record number: had you started to add a record
and then decided not to save it, you would have lost that number. Also, if
you were to use Access Replication, be aware that the autonumber values
would no longer be sequential, but random.

Autonumber fields really aren't intended for "human consumption": their only
intent is to provide a (practically guaranteed) unique value that can be
used as a primary key, and they fill that purpose with or without gaps.

If, after reading all this, you still want to try and use autonumbers and
have their value mean something, you can compact the database after doing
the delete, and the next autonumber assigned should be one more than the
last one used.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Traci" <tm******@earth link.net> wrote in message
news:BW******** *********@newsr ead2.news.atl.e arthlink.net...
If I have a table with an autonumber primary key and 100 records and I delete the last 50 records, the next record added would have a primary key of 101. Is there any way to have the primary key start at 51 after the last 50 records are deleted?

Thanks!

Traci

Nov 13 '05 #2
Sal
Just compact database, next number will be 51

Slawomir Piascik

"Traci" <tm******@earth link.net> wrote in message
news:BW******** *********@newsr ead2.news.atl.e arthlink.net...
If I have a table with an autonumber primary key and 100 records and I delete the last 50 records, the next record added would have a primary key of 101. Is there any way to have the primary key start at 51 after the last 50 records are deleted?

Thanks!

Traci

Nov 13 '05 #3
Actually, This will not recover the autonumbers. It will pick up right from
the last autonumber before the delete.

--
Reggie

----------
"Sal" <sl*****@bellat lantic.net> wrote in message
news:Nu******** *********@nwrdn y01.gnilink.net ...
Just compact database, next number will be 51

Slawomir Piascik

"Traci" <tm******@earth link.net> wrote in message
news:BW******** *********@newsr ead2.news.atl.e arthlink.net...
If I have a table with an autonumber primary key and 100 records and I

delete
the last 50 records, the next record added would have a primary key of

101. Is
there any way to have the primary key start at 51 after the last 50

records are
deleted?

Thanks!

Traci


Nov 13 '05 #4
Traci

In addition to the cautions noted by Doug in his response, be aware that if
your table's primary key (?your Autonumber) is used as a foreign key in
one/more child tables, "restarting " your Autonumbers could hose the
relationship between parent and child records.

--
Good luck

Jeff Boyce
<Access MVP>

Nov 13 '05 #5
I have been reading many posts by users that claim to have years of
experience with Access that promote the use of Autonumber generated
values as primary keys. Don't get me wrong; I use Autonumber values as
primary keys for standalone tables, such as those that really do not
have a primary key unless you use multiple key fields. But I keep
asking myself why anyone would create an architecture of two related
tables upon which the relationship is based on an Autonumber key
field. I'm currently working on one right now where I'm converting an
"Orders" table that uses Autonumber to generate Order numbers. This
Autogenerated value is then stored in the OrderDetails records. If the
scenario that you describe below occurs, guess what? You've just lost
your relationship between the two fields, because you cannot enter
your own values in an Autonumber field, and your OrderDetails table is
useless because the reference to the Orders table is gone. The
OrderDetails table will use an Autonumber key to basically serve as a
surrogate primary key, but I use a "natural" key that I control -
which in this case is my own "rolled" invoice #. The database I'm
converting is going to use my own "rolled" method, upon which a
function will return an incremented value from a table. With the
experience I've had with tables related based on Autonumbers, I will
never willingly design one to be as such.
"Jeff Boyce" <Je**********@m sn.com-DISCARD_HYPHEN_ TO_END> wrote in message news:<O8******* *******@TK2MSFT NGP09.phx.gbl>. ..
Traci

In addition to the cautions noted by Doug in his response, be aware that if
your table's primary key (?your Autonumber) is used as a foreign key in
one/more child tables, "restarting " your Autonumbers could hose the
relationship between parent and child records.

Nov 13 '05 #6
On 16 Jul 2004 21:31:03 -0700, dr*********@hot mail.com (James) wrote:
I have been reading many posts by users that claim to have years of
experience with Access that promote the use of Autonumber generated
values as primary keys. Don't get me wrong; I use Autonumber values as
primary keys for standalone tables, such as those that really do not
have a primary key unless you use multiple key fields. But I keep
asking myself why anyone would create an architecture of two related
tables upon which the relationship is based on an Autonumber key
field.
Because there is absolutely no way that you can define a natural key
that will never change, nor one that is guaranteed to be unique
throughout all of time. Autonumbers are both as long as the
appropriate RI have been established. At least they are supposed to
be - there was a bug in Access 2000 I believe, that has since been
fixed, where autonumbers were duplicated, but that was a bug.
I'm currently working on one right now where I'm converting an
"Orders" table that uses Autonumber to generate Order numbers. This
Autogenerate d value is then stored in the OrderDetails records. If the
scenario that you describe below occurs, guess what? You've just lost
your relationship between the two fields, because you cannot enter
your own values in an Autonumber field, and your OrderDetails table is
useless because the reference to the Orders table is gone. The
OrderDetails table will use an Autonumber key to basically serve as a
surrogate primary key, but I use a "natural" key that I control -
which in this case is my own "rolled" invoice #. The database I'm
converting is going to use my own "rolled" method, upon which a
function will return an incremented value from a table. With the
experience I've had with tables related based on Autonumbers, I will
never willingly design one to be as such.
There have been many threads over the years on this issue. People
line up on one side or the other and have strong feelings about the
mental acuity of those that feel differently. ;-)

But either system works as long as there are enough safeguards built
into the programming. It just takes fewer safeguards if an autonumber
is used, IMO.

mike

"Jeff Boyce" <Je**********@m sn.com-DISCARD_HYPHEN_ TO_END> wrote in message news:<O8******* *******@TK2MSFT NGP09.phx.gbl>. ..
Traci

In addition to the cautions noted by Doug in his response, be aware that if
your table's primary key (?your Autonumber) is used as a foreign key in
one/more child tables, "restarting " your Autonumbers could hose the
relationship between parent and child records.


Nov 13 '05 #7
James

Not sure how this relates to what I interpreted as the original point -- how
can I restart my numbering system? My caution was that any time you
re-initiate a primary key sequence (whether an Autonumber or a "hand-rolled"
custom ID function), you risk orphaning "child" table rows (at best), or
causing totally unrelated child table rows to be connected to the wrong
"parent".

And if there are "child" records, neither an Autonumber or a "hand rolled"
ID function should resequence to "fill in" for missing values in the
sequence. Unless, of course, you have the additional procedures to modify
ALL the child records!

The topic of "natural" keys vs. arbitrary, unique IDs does show up a lot --
and I suspect the discussions border on "religious beliefs". But I'm not
sure that a custom-built procedure that generates a sequential, unique
identifier could be consider a "natural" key.

--
Thanks for the thoughts

Jeff Boyce
<Access MVP>

Nov 13 '05 #8
In message <63************ *************@p osting.google.c om>, James
<dr*********@ho tmail.com> writes
I have been reading many posts by users that claim to have years of
experience with Access that promote the use of Autonumber generated
values as primary keys. Don't get me wrong; I use Autonumber values as
primary keys for standalone tables, such as those that really do not
have a primary key unless you use multiple key fields. But I keep
asking myself why anyone would create an architecture of two related
tables upon which the relationship is based on an Autonumber key
field.
What you have to bear in mind is that there's a difference between a
table and an entity. Entities are in the logical data structure and
tables are in the physical data structure.

Whenever there's a difference between the logical and physical data
structures there is a risk of data corruption.

Joe Celko says that autonumbers don't exist in the logical data
structure, I disagree. I think that autonumbers appear in the LDS as
separate entities with a 1:1 relationship to another entity. The problem
is that that 1:1 relationship isn't inherent in the data, it has to be
enforced by the programmer. If that isn't done then you get situations
where, for instance, a customer's order is entered twice and gets two
different autonumber "Order Numbers."

For that reason it's safest to use a natural key if there is one
available. Adding an autonumber doesn't create a natural key of there
isn't already one in the real data. If there is one already then the
autonumber is probably redundant.

There are times when you need to denormalise the table for performance
reasons, and that may involve adding an autonumber. With current
database systems you are less likely to need this, storage is so cheap
that it's often better to duplicate key fields (even compound keys) than
to add an autonumber.

The most difficult situations arise when you deal with data about
people. There is no simple and easy to use natural key for people. Over
in comp.databases. theory I once suggested that the precise latitude,
longitude, altitude and time of birth would make a good natural key if
we could persuade people to record it.

I'm currently working on one right now where I'm converting an
"Orders" table that uses Autonumber to generate Order numbers. This
Autogenerate d value is then stored in the OrderDetails records. If the
scenario that you describe below occurs, guess what? You've just lost
your relationship between the two fields, because you cannot enter
your own values in an Autonumber field, and your OrderDetails table is
useless because the reference to the Orders table is gone.


That's called an update anomaly, and it's expected when you work with
denormalised data. It's up to the programmers to build system where that
type of anomaly can never occur.

There's also an insert anomaly when the same order gets entered twice.
Again, it's up to the designers to build systems where that can't
happen.

So my advice is take a good long look at the logical data structure and
try very hard to find a natural key. Use that if you can, and only use
autonumbers if you are forced to. There are two reasons for doing that.

The first is performance, to avoid having to match long key fields or to
avoid storing long fields both as primary key and as foreign keys.
Modern systems mean that these cause less problems than they once did.

The seconds is that sometimes you don't have a natural key available,
such as when you are dealing with data about people. At these times you
just have to accept that your data is likely to have errors in it, and
there are limits to what you can do about it. You can choose to use
someone else's autonumber field (for instance a social security number)
or create your own. When you create your own then you need to put
procedures in place to make sure that there is always an exact 1:1
relationship between your autonumber field and the people it is supposed
to identify. You can't do that in software.


--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

Nov 13 '05 #9
James wrote:
I have been reading many posts by users that claim to have years of
experience with Access that promote the use of Autonumber generated
values as primary keys. Don't get me wrong; I use Autonumber values as
primary keys for standalone tables, such as those that really do not
have a primary key unless you use multiple key fields. But I keep
asking myself why anyone would create an architecture of two related
tables upon which the relationship is based on an Autonumber key
field. I'm currently working on one right now where I'm converting an
"Orders" table that uses Autonumber to generate Order numbers. This
Autogenerated value is then stored in the OrderDetails records. If the
scenario that you describe below occurs, guess what? You've just lost
your relationship between the two fields, because you cannot enter
your own values in an Autonumber field, and your OrderDetails table is
useless because the reference to the Orders table is gone.


There's nothing wrong and most things right with Autonumber as a primary
key. The mistake is when people try to do the thing you're talking
about. Once a primary key has been set, it should NEVER change, short
of some sort of massive system-wide upgrade (and then rarely).

Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

33
4283
by: Lee C. | last post by:
I'm finding this to be extremely difficult to set up. I understand that Access won't manage the primary key and the cascade updates for a table. Fine. I tried changing the PK type to number and setting default value to a UDF that manages the auto-numbering. Access won't take a UDF as a default value. Okay, I'll use SQL WITHOUT any aggregate functions, for the default value. Access won't do that either. Okay, I create a second...
4
5162
by: yf | last post by:
A KB article "http://support.microsoft.com/default.aspx?scid=kb;en-us;209599" tells that the maximum number of records that a table may hold if the PRIMARY key data type is set to AUTONUMBER is 4,294,967,295. Suppose the PRIMARY key data type is set to "RANDOM" AutoNumber. Suppose an application (a) successfully INSERTS "X" records, then (b) successfully DELETES "Y" records (X >= Y), then
26
3809
by: jimfortune | last post by:
Sometimes I use Autonumber fields for ID fields. Furthermore, sometimes I use those same fields in orderdetail type tables. So it's important in that case that once an autonumber key value is assigned to a record that it doesn't change. Occasionally I find that due to corruption or an accidental deletion and restore of a record from a backup the autonumber field needs to be tidied up. So when I create (through AddNew) the autonumber...
11
4486
by: Alan Mailer | last post by:
A project I'm working on is going to use VB6 as a front end. The back end is going to be pre-existing MS Access 2002 database tables which already have records in them *but do not have any AutoNumber* fields in them. Correct me if I'm wrong, but I'm assuming this means that I cannot now alter these existing Access tables and change their primary key to an "AutoNumber" type. If I'm right about this, I need some suggestions as to the...
2
1688
by: aharding | last post by:
Is there a way to Autonumber (ex 1, 2, 3 etc) but have it reset to start again at 1 on the change of two fields? Ill try to explain.. A, B, Sub 1 A, B, Sub 2 A, B, Sub 3 C, D, Sub 1 C, D, Sub 2 C, D, Sub 3 C, D, Sub 4
6
11758
by: ashes | last post by:
Hi, I am creating an ecommerce website using Microsoft Visual Studio, VB.Net and MS Access 2003. I am new to VB.Net When someone wants to register on the website, they fill out a form and the contents of the form is inserted into the MS Access database. The Customer table in the database already has 30 records (with CustomerIDs 1 - 30) in it (from when the database was first created). The CustomerID field in the database is an...
0
9057
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8941
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8775
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8781
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7606
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5801
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4536
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2955
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2210
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.