AutoNumber, messed up sequence, duplicate values.

[Iram]

Hello,

I have a database that is used by about 20+ users entering about 1000
records per day. I am noticing that they are having trouble adding records to
the DB sometimes. When they have trouble, I go into the database table and
try to add a record manually at the source and sometimes it tells me that the
Autonumber is creating a duplicate record. As I scrolled through the data in
the table I noticed the following....

Autonumber type field called "RecordID"

1-1085 looks good

then it jumps to 3741 through 3749

then it jumps to 16864 through 17074

I know some people are hitting the Undo button to back out records that are
half way done because they changed their minds about creating those records.
Other people start a record, get about half way and leave the record like
that for about 30 minutes. Would starting a record and not completing it
cause problems for everyone else? Btw I am sharing this db over a possible
inconsistent 1GB WAN connection.


Do you know what is causing the Autonumber to jump around and cause
duplicates?

Your help is greatly apreciated.


Iram

 

[John W. Vinson]

Hello,

I have a database that is used by about 20+ users entering about 1000
records per day. I am noticing that they are having trouble adding records to
the DB sometimes. When they have trouble, I go into the database table and
try to add a record manually at the source and sometimes it tells me that the
Autonumber is creating a duplicate record. As I scrolled through the data in
the table I noticed the following....

Autonumber type field called "RecordID"

1-1085 looks good

then it jumps to 3741 through 3749

then it jumps to 16864 through 17074

Gaps are universal in autonumbers. Just comes with the territory. Deleting a
record will leave a gap; cancelling an entry after it's been started will
leave a gap; running an Append query can leave a BIG gap. If you want to
assign meaning to the numbers, don't use autonumbers!

The duplicate autonumber problem was a bug in some versions of Access.
Compacting the database may help, but you should certainly be sure you have
all the service packs. What version of Access are you running? What service
pack (look at Help... About to see)?

I know some people are hitting the Undo button to back out records that are
half way done because they changed their minds about creating those records.
Other people start a record, get about half way and leave the record like
that for about 30 minutes. Would starting a record and not completing it
cause problems for everyone else?

That will leave a gap in the numbering but should not otherwise cause major
issues.

Btw I am sharing this db over a possible
inconsistent 1GB WAN connection.

Now that's a REALLY BIG problem!!!! Two of them in fact!

Sharing a single database is a good recipe for slow performance, frequent
corruption, and all sorts of issues. A multiuser database should - I'd say
*must* - be split into a shared backend containing only tables, and a frontend
containing links to the tables, along with forms, reports, queries, etc.; each
user gets their own individual copy of the frontend. See
http://www.granite.ab.ca/access/splitapp.htm for a thorough discussion.

EVEN WORSE... Access does *not* "play nice" over a WAN. It's not designed for
it. It works just barely well enough to trick you into thinking it will work,
but you'll have poor performance, risk of corruption, all sorts of problems.
See http://www.members.shaw.ca/AlbertKallal/Wan/Wans.html for Albert's
excellent discussion of the issue and the possible alternatives.

 

[Iram]

We are using Access 2003 (11.8166.8221) SP3 and we are using a split database
with tables in the back end and all other stuff in the front end.