Autonumber-ed primary key field starting from previous value???

[ashish_pal, Delhi]

I have designated one of my fields as autonumber, primarykey.

Everytime is clear the table, and add fresh data to it, either manually or
programmatically, this field starts values ahead from the last value.

Suppose i entered 10 values in the first attempt. then cleared the table,
and re-entered 5 fresh values. these new values will have primary key value
starting from 11 and not from 1, which is what i want.

Why so?

 

[Douglas J. Steele]

Realistically, Autonumber fields have one purpose only: to provide a
(practically guaranteed) unique value that can be used as a primary key. 11,
12, 13, 14, 15 serves that purpose just as well as 1, 2, 3, 4, 5. You
shouldn't care what value is assigned to the Autonumber fields: in fact,
it's rare to show the value of the Autonumber field to the users. No meaning
should be assigned to the value: be aware that they're not guaranteed to be
without gaps (and if you should happen to use replication with your
database, the Autonumber field will be turned to random, so that they won't
necessarily be increasing)

If the value of the Autonumber field matters to you, perhaps you shouldn't
be using an Autonumber field, but instead "roll your own".

Having said all that, in most versions of Access, the Autonumber starting
value will be reset if you compact the database after deleting the records
from the table. Also, check what Allen Browne has at
http://www.allenbrowne.com/ser-26.html

 

[Marco Pagliero]

On 9 Mrz., 12:16, "Douglas J. Steele" wrote

and re-entered 5 fresh values. these new values will have primary key
value starting from 11 and not from 1, which is what i want.

[...]
Having said all that, in most versions of Access, the Autonumber starting
value will be reset if you compact the database after deleting the records
from the table.

Except when I had split the database in front and back end and I
compacted the front end. The first time it took long to realize were
the problem is. Can happen.

Greetings
Marco P

 

[ashish_pal, Delhi]

Thanks Mr. Steele, first of all.

Mine was the same purpose. To allocate each record with a unique index. what
i wanted to know was that is that a design configuration issue or a built-in
Access configuration.
So its the latter one in fact.
You can please explain what you intended by 'compacting the dbase after
clearing table' & 'roll your own' key field.

Regards,
Ashish.
------------------------------------------------------------------------------------

 

[ashish_pal, Delhi]

cheers marco!
-------------------------------------------

On 9 Mrz., 12:16, "Douglas J. Steele" wrote

and re-entered 5 fresh values. these new values will have primary key
value starting from 11 and not from 1, which is what i want.

[...]
Having said all that, in most versions of Access, the Autonumber starting
value will be reset if you compact the database after deleting the records
from the table.

Except when I had split the database in front and back end and I
compacted the front end. The first time it took long to realize were
the problem is. Can happen.

Greetings
Marco P

 

[Douglas J. Steele]

To compact a database, look under Tools | Database Utilities (this is
something you really need to know about!)

By "roll your own" key fields, I was referring to coming up with your own
approach to assigning a unique identifier. How you do that depends on the
situation. If you've only got a single user, it can be as simply as putting
code into the form's BeforeInsert event to determine the highest number used
so far (using DMax) and adding one to it. In a multi-user environment, you
may need to use a more sophisticated approach to avoid duplicates.

 

[BruceM]

This illustrates an approach to incrementing your own number, both in a
single-user and multi-user environment.
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb