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