Resetting Autonumber 1

[votegop]

Problem:
I have table with an autonumber field. I delete the table's records via a Delete Query and then run several append queries. Problem is that the autonumber field does not reset (you'd have to compact after deleting records to do that - not practical). The autonumber field values would eventually be very, very, large numbers, and I assume one day cause a crash.

Solution:
Pretty simple really. I have an identical table with no records ever entered into it. It stays that way. Before appending, I simply copy over the existing table with this:

DoCmd.SetWarnings False
DoCmd.CopyObject , "MyTable",acTable, "MyTable1"
DoCmd.SetWarnings True

MyTable is the table being overwritten with the virgin table MyTable1. Autonumber is now reset.

This is obviously specific to tables where you don't care if the records are deleted. Hope this helps someone.

 

[SeeThru]

An intersting way of resetting an autonumber field.

Just for anyone who doesnt know, the 'normal' way of doing this is to delete all the records from the table, and then use the Compact and Repair tool.

To set it to a specific start number, insert a record (using an append query) with a number ONE LESS than you want to start on. Compant the database and THEN delete the record.

The next one you add will start at the correct number.

 

[JeremyNYC]

Screeeeeeech. Stop. This is predicated on a couple of false assumptions:
1) that you're going to reach the limit of how many records the autonumber can handle. Do that and I'll buy you a shiny new car.

2) that the value of an autonumber matters at all. It doesn't, it shouldn't, it mustn't.

Autonumbers are generated sequentially, but you should NEVER rely on them being sequential in your data. That implies that you should never rely on the number in that field meaning ANYTHING. Those numbers _will be_ unique, but they _will not_, in any database that gets any reasonable amount of work, be sequential. They mean nothing, and if you're relying on the value of that field to communicate something to your users, you're doing them a big disservice. There are TONS of posts about this both here and at comp.databases.ms-access.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[votegop]

Good feedback guys. Thank you. A couple of points...

1) I dont' know if I'd ever get to where I can demand Jeremy's car, but my code is to ensure that I don't ever find out. Just in testing, my autonumber field values (which is not the number of records, because the records get deleted prior to appending) were climbing past 5 digits rapidly. Jeremy's probably right, but the thought of having (after a few year's service) an autonumber field generating numbers in the tens-of-millions just to have a unique identifier seemed silly and potentially hazardous.

2) My autonumber field means nothing other than a unique identifier. You are spot-on, Jeremy.

3) Compacting/repairing frequently in deployed databases often irritates the user who has to sit through it. I was avoiding it.

 

[JeremyNYC]

One more thing to consider: put that table in another database entrirely. Then, instead of deleting the records and appending records, you can just blow away the old (external) database and then make a copy of a template database, one that holds an empty version of the table. That will take care of the bloat that comes with deleting records and adding new ones.

Also, append some records with very larget numbers in the autonumber field. I think you'll see that it's mathematically comfortable for me to offer up a car.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Gazonice]

Hello,

I use this piece of SQL to set the starting number of an Invoicing database:

DoCmd.RunSQL "insert into tbl_invoice_number (invoice_number) values ([starting_invoice_no] - 1)"

[starting_invoice_no] is the field that collects the first invoice number from the user.. you could replace this with a value of your choosing.

Garry

 

[SgtJarrow]

Jeremy,

Tried to beat you on your offer of a car. But I reached the database limit of 2GB before I ran out of Autonumbers...and I only had on table with two fields in the database.....Nice!

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[JeremyNYC]

Robert,

Darn. I was all set to go to the toy store and buy a shiny new matchbox!

If you've ever played with random autonumbers (you can do that in the form design), you'll see why I was so comfortable making that offer. It's just not a concern.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.