AutoNumber Field (Access 2007)

[Jeff Gaines]

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?

 

[Golfinray]

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.

 

[NTC]

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]

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?

 

[Allen Browne]

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.

 

[Jeff Gaines]

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