AutoNumber, messed up sequence, duplicate values.
in [Forms]
|
Prev: Why is my DB always crashing when clicking the dropdown on ComboBo
Next: Iteration through a multi-Select List box
From: Iram on 2 Jun 2010 18:58 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
From: John W. Vinson on 2 Jun 2010 19:16 On Wed, 2 Jun 2010 15:58:21 -0700, Iram <Iram(a)discussions.microsoft.com> wrote: >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. -- John W. Vinson [MVP] > > >Do you know what is causing the Autonumber to jump around and cause >duplicates? > >Your help is greatly apreciated. > > >Iram
From: Iram on 2 Jun 2010 23:28
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. "John W. Vinson" wrote: > On Wed, 2 Jun 2010 15:58:21 -0700, Iram <Iram(a)discussions.microsoft.com> > wrote: > > >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. > -- > > John W. Vinson [MVP] > > > > > >Do you know what is causing the Autonumber to jump around and cause > >duplicates? > > > >Your help is greatly apreciated. > > > > > >Iram > . > |