On Wed, 2 Jun 2010 15:58:21 -0700, Iram <(E-Mail Removed)>
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
Similar Threads
