I have setup a table for the purpose of generating unique numbers that can be used accross several recordsets. To obtain a unique number, I insert the current users name into tblGlobalRefNo, obtain the unique ID (which is set to autonumber, indexed (yes and no duplicates), set the unqiue ref no. in the current recordset, then delete the record I created in tblGlobalRefNo. However, if I close the database, then load it up again, tblGlobalRefNo is acting as if there was never any records inserted into it, as the ID starts at 1 again when I insert a new record.
Can anyone help? This is driving me mad. The only way I can think of to get round this would be to keep the numbers in the table, and just let it grow, but this isn't ideal.
when you run out of autonumber space between two and three billion records, the autonumber part of your table will be 12GB ...IMHO not a lot of overhead on a multi-billion record database that in any case would have brought A to it's knees long before you got there.
...so first thought is don't add the user name and keep the autonumbers (they only cost 4 bytes each).
second thought is if you really are trying to get a three billion record database onto a single floppy (joking!!!), consider deleting the oldest 80% of your tblGlobalRefNo as part of your maintenence routine... you are going to have to compact anyway to enjoy any benefit from the delete, so another few lines of code wont hurt.
Unless your IDs need to be sequential (which is not good practice), then you should be able to get around this by setting the autonumber field to generate random numbers instead of sequential.
Sequential autonumber fields are reset to the lowest existing value + 1 whenever the database is compacted. Thus, even if you find a way to retain your sequential values between calls, your numbering will restart at 1 if you ever compact your database.
I don't know all the requirements of your application, but I'd think a more direct method would be to create a VB function that returns a random number or a GUID value. This is the method I have used in the past.
Thanks for the replies. I hadn't really thought about it in those terms (as far as size was concerned). Actually, I was probably more concernced with lookup times. This database only allows 1 session for each user. When the user creates a record, their LAN ID (taken from fOSUserName) is added to tblGlobalRefNo. I then run some code to get the unique auto-number that corresponds to their unique LAN ID. Once I have that unique global ID (which I have to have a field for in tblGlobalRefNo because thats how I'm retrieving the unique ID I'm creating), I delete the record in tblGlobalRefNo. If I have to keep all the numbers, then I will just Null the user name after obtaining the ID from the auto-number. However, I'm concerned that this may eventually slow this insert/select process.
That said, I'm not expecting more than a couple of hundred records to be created in this databse per week, so maybe I'm worrying over nothing here... It just seems like a bad way to run things. In fact, I'm really surprised to learn that Auto-Number acts in this way.