A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

AutoNumber Field (Access 2007)



 
 
Thread Tools Display Modes
  #1  
Old October 28th, 2008, 04:53 PM posted to microsoft.public.access.tablesdbdesign
Jeff Gaines
external usenet poster
 
Posts: 23
Default 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  
Old October 28th, 2008, 06:56 PM posted to microsoft.public.access.tablesdbdesign
Golfinray
external usenet poster
 
Posts: 1,597
Default 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  
Old October 28th, 2008, 10:31 PM posted to microsoft.public.access.tablesdbdesign
ntc
external usenet poster
 
Posts: 130
Default 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  
Old October 29th, 2008, 12:20 PM posted to microsoft.public.access.tablesdbdesign
Jeff Gaines
external usenet poster
 
Posts: 23
Default 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  
Old October 29th, 2008, 01:00 PM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default 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  
Old October 29th, 2008, 04:39 PM posted to microsoft.public.access.tablesdbdesign
Jeff Gaines
external usenet poster
 
Posts: 23
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:40 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.