If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Missign Autonumber records
I have a table setup to list incidents and the primary key is an autonumber
field called indicent number. I have noticed going through the records, there are missing numbers (ie. there are numbers missing from the sequential order). The database is currently setup (I know this isn't the best programming but I have to do it to get around our school system's computer setup) with the whole database on the server and everyone accessing the whole database with limited menus. Is this a record lock situation where two people happen to try to enter a new record at the same time or is this another issue? Some teachers have told me they entered data and it is not there. That is how I found the missing numbers. |
#2
|
|||
|
|||
Autonumbers will have gaps in the sequence because, once "used", a number
cannot be reused. An autonumber is used whenever you create and save a record, create and discard a record, delete an existing record, initiate an append query and then cancel the query, and so on. If you need to have a "guarantee" of sequential numbers without gaps, you will need to use a numeric field whose value for new records is established by programming. -- Ken Snell MS ACCESS MVP "Eduman" wrote in message ... I have a table setup to list incidents and the primary key is an autonumber field called indicent number. I have noticed going through the records, there are missing numbers (ie. there are numbers missing from the sequential order). The database is currently setup (I know this isn't the best programming but I have to do it to get around our school system's computer setup) with the whole database on the server and everyone accessing the whole database with limited menus. Is this a record lock situation where two people happen to try to enter a new record at the same time or is this another issue? Some teachers have told me they entered data and it is not there. That is how I found the missing numbers. |
#3
|
|||
|
|||
Eduman,
The gaps in the autonumber are normal; in an autonumber field, once a number is issued it can't be used again if the record is deleted (even if it the last one). A number is issued (and "spent") even if you just Esc twice out of a new record in a form. Autonumbers are not suited for sequential numbering, they are just not meant for that, they are only meant as unique identifiers. If you absolutely must have sequential numbering, then (assuming all data entry is done through a form) the way to go is to use a simple number field instead, and use an expression on the form to calculate the next available number, like DMax("[PKField]", "MyTable") + 1. This could be in the form control's Default value property, or you could use a macro or some VBA code fired off a form event to set the value. In a multiuser environment, the timing when this happens is of particular concern, in that if you use the default value property, if a user starts to enter a new record the next available number is calculated right as they enter the new record; so, if another user starts a new record before the first one has saved theirs, they will end up with the same PK value, so the second one won't be able to save their record. To avoid this, I would use an unbound form, and some VBA code behind a Save command button to add the record, so the next available value can be calculated right before the saving, rather than when entering the new record in a bound form. Timing issues like the one I described above are quite likely the reason why users can't find some records they (beieve they) have created; in fact, this is much more likely a reason for that than the monolithic multi-user structure. That said, under such a scenario (monolithic multi-user) the real danger is corruption; you may not experience it for a while (sometimes a months long while!) but eventually it will strike, and when it does so once then there is no end to it. The bottomline is, you must absolutely split and give each user their own copy of the front end. If this impossible (why?) then I strongly suggest you take measures to ensure there are never more than a single user in the database at any time. HTH, Nikos Eduman wrote: I have a table setup to list incidents and the primary key is an autonumber field called indicent number. I have noticed going through the records, there are missing numbers (ie. there are numbers missing from the sequential order). The database is currently setup (I know this isn't the best programming but I have to do it to get around our school system's computer setup) with the whole database on the server and everyone accessing the whole database with limited menus. Is this a record lock situation where two people happen to try to enter a new record at the same time or is this another issue? Some teachers have told me they entered data and it is not there. That is how I found the missing numbers. |
#4
|
|||
|
|||
Thanks for the help. The reason I have not split the database is because the
school system has so many levels of security tied in that I cannot access it as the database keeper. They will not issue me full license over the shared network and the updating and upkeeping need to be done by me. As for setting up the system so that only one person can use it at a time, is there a way to enter data and then "save" the record so that only one person at a time can save as opposed to autosaving as a field is updated? I don't want to limit the database to only one person opening it at a time on one machine in the entire building. That would be counterproductive to the purpose of my creation. "Nikos Yannacopoulos" wrote: Eduman, The gaps in the autonumber are normal; in an autonumber field, once a number is issued it can't be used again if the record is deleted (even if it the last one). A number is issued (and "spent") even if you just Esc twice out of a new record in a form. Autonumbers are not suited for sequential numbering, they are just not meant for that, they are only meant as unique identifiers. If you absolutely must have sequential numbering, then (assuming all data entry is done through a form) the way to go is to use a simple number field instead, and use an expression on the form to calculate the next available number, like DMax("[PKField]", "MyTable") + 1. This could be in the form control's Default value property, or you could use a macro or some VBA code fired off a form event to set the value. In a multiuser environment, the timing when this happens is of particular concern, in that if you use the default value property, if a user starts to enter a new record the next available number is calculated right as they enter the new record; so, if another user starts a new record before the first one has saved theirs, they will end up with the same PK value, so the second one won't be able to save their record. To avoid this, I would use an unbound form, and some VBA code behind a Save command button to add the record, so the next available value can be calculated right before the saving, rather than when entering the new record in a bound form. Timing issues like the one I described above are quite likely the reason why users can't find some records they (beieve they) have created; in fact, this is much more likely a reason for that than the monolithic multi-user structure. That said, under such a scenario (monolithic multi-user) the real danger is corruption; you may not experience it for a while (sometimes a months long while!) but eventually it will strike, and when it does so once then there is no end to it. The bottomline is, you must absolutely split and give each user their own copy of the front end. If this impossible (why?) then I strongly suggest you take measures to ensure there are never more than a single user in the database at any time. HTH, Nikos Eduman wrote: I have a table setup to list incidents and the primary key is an autonumber field called indicent number. I have noticed going through the records, there are missing numbers (ie. there are numbers missing from the sequential order). The database is currently setup (I know this isn't the best programming but I have to do it to get around our school system's computer setup) with the whole database on the server and everyone accessing the whole database with limited menus. Is this a record lock situation where two people happen to try to enter a new record at the same time or is this another issue? Some teachers have told me they entered data and it is not there. That is how I found the missing numbers. |
#5
|
|||
|
|||
Eduman,
One person at a time being counerproductive is the understatement of the year! I couldn't agree more... it was a 'last resort' suggestion. If what's preventing you from having a real mutli-user split database is network security, then all that's required is a request to the IS administrator for a folder with full rights to all database users; it's not hard to do! Even so, thought, the timing issue to avoid double issuing of a next number is still valid; like I said in my previous post, my preferred solution is the use of an unbound form and some simple code (which I can help you with) to calculate the next available PK value right before the record is saved, so the chance of double issuing is practically zero. Your implied alternative of a locking at form level so only a user can enter new records at a time is quite easy to implement: add a single field (Yes/No, default No) table to record the action of a user entering records; use the form's On Current event to check the value when going to a new record, prevent new record enrty if the value in the table field is Yes, set the value to Yes if previously No, reset it to No when going to another record (which is not a new one again) or closing the form *if* the lock was set by the same user (use a hidden control on the form to "remember" if the lock was set by this user)... quite easy to do with some simple code, but still only a half-measure in my humble opinion, and still impeding productivity since you can't have several users inserting data. HTH, Nikos Eduman wrote: Thanks for the help. The reason I have not split the database is because the school system has so many levels of security tied in that I cannot access it as the database keeper. They will not issue me full license over the shared network and the updating and upkeeping need to be done by me. As for setting up the system so that only one person can use it at a time, is there a way to enter data and then "save" the record so that only one person at a time can save as opposed to autosaving as a field is updated? I don't want to limit the database to only one person opening it at a time on one machine in the entire building. That would be counterproductive to the purpose of my creation. "Nikos Yannacopoulos" wrote: Eduman, The gaps in the autonumber are normal; in an autonumber field, once a number is issued it can't be used again if the record is deleted (even if it the last one). A number is issued (and "spent") even if you just Esc twice out of a new record in a form. Autonumbers are not suited for sequential numbering, they are just not meant for that, they are only meant as unique identifiers. If you absolutely must have sequential numbering, then (assuming all data entry is done through a form) the way to go is to use a simple number field instead, and use an expression on the form to calculate the next available number, like DMax("[PKField]", "MyTable") + 1. This could be in the form control's Default value property, or you could use a macro or some VBA code fired off a form event to set the value. In a multiuser environment, the timing when this happens is of particular concern, in that if you use the default value property, if a user starts to enter a new record the next available number is calculated right as they enter the new record; so, if another user starts a new record before the first one has saved theirs, they will end up with the same PK value, so the second one won't be able to save their record. To avoid this, I would use an unbound form, and some VBA code behind a Save command button to add the record, so the next available value can be calculated right before the saving, rather than when entering the new record in a bound form. Timing issues like the one I described above are quite likely the reason why users can't find some records they (beieve they) have created; in fact, this is much more likely a reason for that than the monolithic multi-user structure. That said, under such a scenario (monolithic multi-user) the real danger is corruption; you may not experience it for a while (sometimes a months long while!) but eventually it will strike, and when it does so once then there is no end to it. The bottomline is, you must absolutely split and give each user their own copy of the front end. If this impossible (why?) then I strongly suggest you take measures to ensure there are never more than a single user in the database at any time. HTH, Nikos Eduman wrote: I have a table setup to list incidents and the primary key is an autonumber field called indicent number. I have noticed going through the records, there are missing numbers (ie. there are numbers missing from the sequential order). The database is currently setup (I know this isn't the best programming but I have to do it to get around our school system's computer setup) with the whole database on the server and everyone accessing the whole database with limited menus. Is this a record lock situation where two people happen to try to enter a new record at the same time or is this another issue? Some teachers have told me they entered data and it is not there. That is how I found the missing numbers. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I get the sum from a column and display the sum? | jon | General Discussion | 9 | March 11th, 2005 05:22 PM |
How to sort/update large excel db | ConfusedNovice | General Discussion | 15 | February 2nd, 2005 12:43 AM |
Attn Sprinks- Not duplicate insert records | babs | Using Forms | 1 | December 13th, 2004 06:25 PM |
Problem with AutoNumber | accessmonk | Database Design | 2 | September 30th, 2004 08:57 PM |
selecting multiple records | sps | Using Forms | 3 | August 3rd, 2004 08:22 PM |