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
|
|||
|
|||
AutoNumber Field (Access 2007)
I am having some problems with a table in an Access 2007 database which has an AutoNumber field to keep a record number. It holds a record of various actions that will be required at the end of each day and gets filled from a C# program using ADO.NET. The issue seems to be that new records are being created in batches and, with the speed of modern computers, the table doesn't seem able to keep up - it complains that I am duplicating record numbers in the AutoNumber field. I could just make it an indexed (no duplicates) field and use the SQL MAX statement to get the highest record number then increment it by one and assign it to the new record. I have posted some code in an ADO.NET news group but would appreciate any thoughts on the use of AutoNumber fields generally. They seem to be useful but Googling throws up a lot of problems, although many of them are about the format of the field or the starting number used. Should I just drop the AutoNumber field or is it actually the best way to ensure each record has a unique number? -- Jeff Gaines Damerham Hampshire UK You can't tell which way the train went by looking at the tracks |
#2
|
|||
|
|||
AutoNumber Field (Access 2007)
Autonumber fields are not designed to be sequential. For example, if you
delete a record autonumber is gone. It would be better to build yourself a record number. Something like: ALTER table yourtablename add column id counter (10000,1000) This would make numbers from 1000 to 10000 in your table in a new column. You could then set that datatype to number instead of autonumber. "Jeff Gaines" wrote: I am having some problems with a table in an Access 2007 database which has an AutoNumber field to keep a record number. It holds a record of various actions that will be required at the end of each day and gets filled from a C# program using ADO.NET. The issue seems to be that new records are being created in batches and, with the speed of modern computers, the table doesn't seem able to keep up - it complains that I am duplicating record numbers in the AutoNumber field. I could just make it an indexed (no duplicates) field and use the SQL MAX statement to get the highest record number then increment it by one and assign it to the new record. I have posted some code in an ADO.NET news group but would appreciate any thoughts on the use of AutoNumber fields generally. They seem to be useful but Googling throws up a lot of problems, although many of them are about the format of the field or the starting number used. Should I just drop the AutoNumber field or is it actually the best way to ensure each record has a unique number? -- Jeff Gaines Damerham Hampshire UK You can't tell which way the train went by looking at the tracks |
#3
|
|||
|
|||
AutoNumber Field (Access 2007)
G'rays advice is correct. Autonumber/no duplicates is ideal to be sure there
is a unique value...but there is no guarantee it will be sequential. Your requirement to identify the Max - depends on really what you consider to be Max.... you could make a field that simply is a timestamp "Jeff Gaines" wrote: I am having some problems with a table in an Access 2007 database which has an AutoNumber field to keep a record number. It holds a record of various actions that will be required at the end of each day and gets filled from a C# program using ADO.NET. The issue seems to be that new records are being created in batches and, with the speed of modern computers, the table doesn't seem able to keep up - it complains that I am duplicating record numbers in the AutoNumber field. I could just make it an indexed (no duplicates) field and use the SQL MAX statement to get the highest record number then increment it by one and assign it to the new record. I have posted some code in an ADO.NET news group but would appreciate any thoughts on the use of AutoNumber fields generally. They seem to be useful but Googling throws up a lot of problems, although many of them are about the format of the field or the starting number used. Should I just drop the AutoNumber field or is it actually the best way to ensure each record has a unique number? -- Jeff Gaines Damerham Hampshire UK You can't tell which way the train went by looking at the tracks |
#4
|
|||
|
|||
AutoNumber Field (Access 2007)
On 28/10/2008 in message
NTC wrote: G'rays advice is correct. Autonumber/no duplicates is ideal to be sure there is a unique value...but there is no guarantee it will be sequential. Your requirement to identify the Max - depends on really what you consider to be Max.... you could make a field that simply is a timestamp Many thanks Golfinray and NTC :-) After a couple of weeks puzzling over this I have discovered that Access has its knickers in a twist in respect of where it is for the Autonumber field. I had added a couple of records manually as a test then I noticed it was allocating record numbers from 103 on whereas the highest record number is 827. As soon as I tried to add a fifth record I got the same error from Access as I got from my code - so 2 weeks thinking my code was wrong and it was an Access problem :-( Is there a way I can persuade Access to sort its auto-numbering out? Fortunately for this table the record numbers don't matter but for the other 6 tables in this database the record numbers are used as indexes into other tables so it is important that they don't change. On a general point - if Access can get confused like this is it better to set up my own fields/indexes to relate records to each other, would that be more robust? -- Jeff Gaines Damerham Hampshire UK You can't tell which way the train went by looking at the tracks |
#5
|
|||
|
|||
AutoNumber Field (Access 2007)
Try a compact repair, and see if that fixes it.
If not, run the code in this link: Fixing AutoNumbers when Access assigns negatives or duplicates at: http://allenbrowne.com/ser-40.html The article also explains this causes, so you know what you are dealing with. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Jeff Gaines" wrote in message ... On 28/10/2008 in message NTC wrote: G'rays advice is correct. Autonumber/no duplicates is ideal to be sure there is a unique value...but there is no guarantee it will be sequential. Your requirement to identify the Max - depends on really what you consider to be Max.... you could make a field that simply is a timestamp Many thanks Golfinray and NTC :-) After a couple of weeks puzzling over this I have discovered that Access has its knickers in a twist in respect of where it is for the Autonumber field. I had added a couple of records manually as a test then I noticed it was allocating record numbers from 103 on whereas the highest record number is 827. As soon as I tried to add a fifth record I got the same error from Access as I got from my code - so 2 weeks thinking my code was wrong and it was an Access problem :-( Is there a way I can persuade Access to sort its auto-numbering out? Fortunately for this table the record numbers don't matter but for the other 6 tables in this database the record numbers are used as indexes into other tables so it is important that they don't change. On a general point - if Access can get confused like this is it better to set up my own fields/indexes to relate records to each other, would that be more robust? -- Jeff Gaines Damerham Hampshire UK You can't tell which way the train went by looking at the tracks |
#6
|
|||
|
|||
AutoNumber Field (Access 2007)
On 29/10/2008 in message Allen
Browne wrote: Try a compact repair, and see if that fixes it. If not, run the code in this link: Fixing AutoNumbers when Access assigns negatives or duplicates at: http://allenbrowne.com/ser-40.html The article also explains this causes, so you know what you are dealing with. Allen you have saved my reputation in the eyes of my daughter (my client in this case)!!! Not only did your macro fix the issue (just one table) but your notes - Other Scenarios has enabled me to make a pretty good stab at why it happened. I produce boiler plate code for data access from another C# program I wrote and that had a problem, in short there were circumstances in which it would write a record number of '-1' to the AutoNumber field, and Access happily let it. At some stage this has happened with this app, and although I corrected it it looks like it screwed the table up. The biggest irony though is when I attempted to add the URL of your site to my (self written) note book app that fell over as well due to exactly the same issue and the same underlying cause, I clearly have a bit of work to do still correcting some of my code! Many thanks :-) -- Jeff Gaines Damerham Hampshire UK 640k ought to be enough for anyone. (Bill Gates, 1981) |
Thread Tools | |
Display Modes | |
|
|