How to stop Autonumber from reseting when moving db 2

[Simon22]

Hi all,
Can anyone tell me why Access is reseting my autonumber fields to 0 everytime I move the db from one computer to another? Can I stop it from doing this? It is messing up my order system because the reset autonumber is making duplicate entries in my primary key field.
Thanks for the help

 

[JimHorton]

Simon,
Oh, brother...this is one of my pet peeves against Access--the whole concept of Autonumber. If they'd just make a sequence construct such as Oracle it would mean a tiny bit more work on the front side but millions fewer headaches. When deleting data, then compacting, the autonumber is reset. If you just move the .mdb file from one machine to the other, the autonumber won't be reset. If you just import the table, the autonumber should remain intact as well.
--Jim

 

[Simon22]

IC - so I guess I shouldn't compact and repair this thing. Too bad..
Thanks for the reply.

 

[JimHorton]

You can compact and repair--if the data is still in the tables. But if you have, say, a last record with autonumber ID of 100, then delete it, THEN you compact, then next record is 100 again, but then any child records attempted will try to have 100 as the fk, which could mean a duplicate, depending on the relationship/key structure. If you have a relationship that causes this, you need to set up the ref. integrity so that you can cascade the deletes of the parent record, this way you'll prevent any duplicates when the autonumber resets. There are faq's here on how to create your own sequence system--I don't have the faq #'s here, but if you search in faq's for 'autonumber', I think several will show up.
--Jim

 

[JerryDennison]

It is clear you do not understand the purpose of autonumber to begin with. The autonumber is not meant to have meaning. In fact, the autonumber field (especially as it relates to use as a primary key) is not meant for "human consumption". It is an address for the entity record and is strictly for Access's use. "Housekeeping" as it were. If you need sequential numbers (i.e. check register type numbers or PO's or some such) you should generate and control them yourself.

 

[JimHorton]

Jerry,
I'm not sure to whom your post was directed, but I do understand the purpose of autonumber, which is precisely why it's one of my pet peeves--the fact that it *is* used for human consumption, even though it's purpose is not meant to be this--too many people lazily rely upon it as a quick & dirty 'sequence' construct which it is not. As you know, Oracle has 'rowid', which we aren't allowed to use or view--that's the way it should be.

I believe MS was fully aware of how autonumber would be used and if I recall they even encouraged it's use in early docs. I believe they made a marketing decision and felt that a reputaion for Access of 'ease of use' would be preferrable to a reputation of integrity. Imagine the number of posts here and other forums of angry beginners complaining of 'duplicate value' errors, etc, because they didn't use autonumber and didn't know how to set up whatever alternative sequencing scheme MS might have come up with. That decision certainly helped Access' market share, which in turn helped the employment prospects of Access developers, so I guess I have to accept the good with the bad, though on the other hand, if Access had less market share, then something else would have filled in. It just goes to show there is no altruism in the marketplace.
--Jim

 

[Simon22]

Thanks for the help guys but this is out of my league. See y'all round.

 

[JerryDennison]

Jim, I apologize if you feel my comments were directed at you they were not, I was not directing my comments toward the original poster.

 

[Simon22]

What I know of Autonumbers comes from the MS Access 2000 Help file. Blame it on the designers. Actually, I'm taking a db desing course right now (Dalhousie University) and they use the Autonumber the same way as the Access help file. I think that JimHorton is correct. Access was designed and marketed for me (Regular Guy). If they had intended the Autonumber to be used in some other way, they would have described it differently within the program. As for other methods of generating numbers, I don't have the expertise to create them. Like I said, I'm not building complex, corporate databases; I just need to be able to create simple dbs.

 

[JerryDennison]

Clearly, I've come across the wrong way. For this I apologize. The Access autonumber does exactly what it was designed to do. Create a unique (incremented or random) number for each record as it is created. Once a number is assigned it can no longer be used (whether or not the record is saved) again (technically this is not exactly right but the gist of it is). Because of this, the autonumber is not particularly suited for things like check numbers, etc. It's best and primary usage is as an address (primary key) for the record itself.

 

[Simon22]

No apology needed

That is what I was using it for though. The Autonumber is used as the primary key in my COrders table.

COrder_Num (Autonumber) (PK)
COrder_Date (Date)
Client_ID (FK)

With related table COrderLines
COrder_Num (Number)(FK)(PK)
CLine_Num (Number)(PK)
Prod_ID (FK)
CLine_Units
CLine_Price
etc.

The problem I had was that the Autonumber was reseting to 1 when I compacted. It would be fine if it reset to the next seqential number but that's not the case. For some reason it set to 1 so it would try to write over my existing COrder_Num values (well, it would try to overwrite but generated a duplicate error).

I probably should have been clearer in my original post.

 

[JerryDennison]

Make sure you enforce referential integrity cascade updates/deletes between the order and it's details. This should eliminate your problem. Autonumber will only reset to 1 if there are no records in the table.

 

[JimHorton]

Simon,
Autonumber will also reset upon compaction if any records were deleted from the end of the list. It's a dangerous construct to use, but if, as has been said, you cascade deletes, you should be safe.

However...I've spoken with many panicked user who figured they were just deleting a header record that they 'were just going to recreate', wondering why hundreds of detail and sub-detail records were missing--'all orders, details, payments, etc are now gone from this customer!!'.

They innocently clicked 'ok' to the 'are you sure' message. This message of course, upon closer reading, lightly mentions 'including related records'.
'What does that mean?'

So you can see that there is danger all about. My general rule is not allow users direct access to tables, and then I have control over data operations, such as updates deletes via a Form, and I can give a stronger message and log the action. However, the quick & dirty way finds most Access apps with free access to table datasheets (deleting, updating, etc, with no audit trail or validation to speak of), and in these cases it's probably better to *not* cascade deletes, and let the message come up saying "you can't delete because the child records need a parent " or whatever it says, and make the user spend a little time and thought as to what he *really* is about to delete.
--Jim