Auto Number start point (Access 2000)

Topic

New Reply

I have a table that uses AutoNumber in primary key field. There are no records in the table, I have deleted any records created while testing. I now need to start AutoNumbering from a specific number. I have copied the table structure, appended a record with the number previous to my start point into my temporary table and then appended this to my empty table.
I thought this would allow me to use AutoNumber and assign the next in sequence, apparently I was wrong! I still get numbers in sequence from the test records. Any thoughts?

Reply | Quote

Replies

Compact and repair your database with no records in the table. If you have a record with the ID number one less than you want to start with, that process will reset the counter to give you the next number in seqence. Unfortunately you can’t specify the number you want to start with like you can in SQL Server.

Reply | Quote

I’ll share my thoughts. Don’t use autonumbering if you want to control numbering. Using autonumbering means that you don’t care what the numbers are as long as they are unique, since that is what is needed for a primary key. Don’t show the user the autonumber on a form – it’s just an internal thing.

Reply | Quote

If you’d like it to restart the autonumbering at 1, with the table cleared of any records, choose to compact and repair the database.

However, if you’d like to begin at a number other than 1, or increment the autonumber value by something other than increments of 1, I believe you have to make use of the ADOX library.

Reply | Quote

Hi Mark,

I agree with Claus about not using AutoNumber for creating specific numbers in most cases – I use =DMax(“”,”

“) + 1

for creating an ID number that’s one higher than the last.

However, just for information, you can have AutoNumber start at a given value (such as 1000) by entering the start value in the Format field property (in the Table Design).

HTH

Reply | Quote

Thanks for the advice, as you can tell by my questions I am a new user and prone to making some bad decisions in database design. After viewing the posts I realize I have created a bit of a mess and need to change my design.
I have many tables (some populated with data) and relationships set up, how will changing these key fields affect the database? Am I in a “start-over” situation or can I create new key fields that are AutoNumbers and change my current key fields to Numbers? Any suggestions?
PS: where did you get the Indiana state flag, that’s pretty cool.
Thanks,
Mark

Reply | Quote

I have to go on record as disagreeing with Claus and Mark on autonumbers. I use them extensively because they are not data, they are merely values that serve as keys. My users don’t see them and they are only used internally.

That said, there is NO reason to start an autonumber at a particular point. Compacting the database will reset the autonumber seed value in an empty table, just as Wendell and Shane have suggested. However, unless you have applied at least SR1 to your Office 2000 installation, appending a record to the autonumber field will *not* reset the seed for the next number. That was a bug in the initial version of Access 2000 that was cured by the service release.

Reply | Quote

I’ll side with Charlotte on this as well. In particular, the technique Mark uses can create race conditions where you end up with duplicate key errors because somebody started an insert, got interrupted (or just typed slower than the other person), and another person adds a record before the other person finishes. IF you insist on controlling the numbering, you should add a table which contains the next record number available, do an update on it with edit locks to increment it by one, and then use the number to key your record with. But it’s lots more work than an autonumber.

Reply | Quote

It’s almost lunchtime and I’m so hungry I could eat my words!

On second thought, I agree with Wendell and Charlotte.

The technique I mentioned will only work with Single-user situations. A better solution would be to have an Autonumber field for the Primary Key, then use the DMax formula as the default value for a different “ID” field (such as Invoice Number, etc.).

Reply | Quote

I got the impression that’s pretty much what Claus meant – don’t use autonumbers for meaningful numbers, only to provide unique keys for internal use.

Reply | Quote