I have a very basic database with only one main table and a few lookups - no relationships.
the Autonumber field in the main table has little code associated with its form however I am noting that every 10 records or so it skips a number.
I have locked the DB up pretty tight from the users so they have no delete access and can only modify records very sparingly once created. they have no way to delete a wrong entry - they must tick a box called CANCELED in order to remove the entry from the list and start again. - the ONLY way to delete a record is to SHIFT-OPEN, open the table and delete from there... I doubt they are doing that but anything is possible...
Question is this - I have seen numerous web discussions on similar issued but the solutions generally point to some code or a formatting issue or a SQL / Access thing... I have no such system... its a straight Front end / back end DB using linked tables on the local network. Can someone please advise if this is just an Access thing and just to ignore it or is this very unusual and something is going on in that someone IS deleting records... if someone IS deleting records - is there any way I can maybe PW protect if it tries to open in edit mode? Or can I PW protect the table itself maybe?.
Or even better - is there a way I can maybe add some fields and code and see what the heck is going on? whether it is access just not creating that # or if someone is messing with me? Thanks
The basic rule for database auto numbers is simply they are INTERNAL numbers – END of story! I mean when you load a word document do you care about the memory segment number used? Auto numbers are used to setup relations between tables. They are a “concept” and if the tables are linked by pictures, apes in the jungle eating bananas or some auto numb sequence, you do NOT care.
However, to answer your question if you jump to a new record, and then start typing, the record is dirty. Of course the user might decide, hey, I don’t want to add this record. If they go edit->undo, or hit control-z and then exit, the record is not created nor is it saved. However, the auto number will get incremented. I mean since the database is multi-user, then one user starts working, and then another – they will both be assigned an auto number – but both may decide to not save.
Auto numbers are NOT to be given meaning to the end user, and really they should never seem them. Users never see the computer memory segment that a record or word document loads into also – they don’t care.
How internal indexing and how tables are laid out, and how they work are the SOLE issue of the database engine, and have ZERO to do with you, or your users.
Now of course you are “aware” that your computer has memory, but you would NOT expose the “memory” location used to your end users, since such internal housekeeping numbers are just that – internal housekeeping numbers.
In addition to users hitting un-do and bailing on record addition, general deleting of records will also produce gaps.
If you looking for some kind of number sequence, then create an invoice number field, or whatever. While an invoice number can be required, if you use internal auto numbers, then your database design can function because you don’t have some Social insurance number, or some silly invoice number. What do they have to do with you as the developing building relations between tables? (Answer: absolute nothing at all!!!)
The fact that your database functions fine without an invoice number or other numbers has ZERO to do with internal numbers used for housekeeping and to maintain relationships.
You define relationships in your database – these have ZERO to do with what your users think about, know about etc. Such numbers have no more meaning then the memory segment used in your computers ram to load a record into.
If you need some kind of invoice number, or some other sequence number, then you have to add that design part to your database. Such numbers have ZERO to do with some internal numbers that Access uses and maintains to build relationships with.
In a multi-user environment, and due to additions or deletions, you as a general rule might as well assume auto numbers are random – they have no meaning to users, nor any to business rules that require some kind of sequence number.