Access 2000: Duplicate Values in Index Problem

[Bari Allen via AccessMonster.com]

I have a database in a Front-End/Back-End User Environment, which has been
running fine, for 4 years. Lately, when the users try to enter data (from
a form into a single table) they've been getting the message that they
can't add the records, because it would create "duplicate Values in the
Index". The table's Primary Key is an AutoNumber Increment field. To
solve the problem, I copied the table structure to a new table, and
appended all of the records, rebuilding the new table's index from scratch.
This worked fine, for 2 weeks. Now, suddenly, the message is showing up,
again. The table presently contains over 900 records, however the index
wants to jump back to somewhere around 700. I "fixed" the problem, the
same way, again, but would like to know what may be causing this, and how I
can prevent it from continuing to happen. Thanks.

--
Message posted via http://www.accessmonster.com

[MGFoster]

Bari Allen via AccessMonster.com wrote:

I have a database in a Front-End/Back-End User Environment, which has been
running fine, for 4 years. Lately, when the users try to enter data (from
a form into a single table) they've been getting the message that they
can't add the records, because it would create "duplicate Values in the
Index". The table's Primary Key is an AutoNumber Increment field. To
solve the problem, I copied the table structure to a new table, and
appended all of the records, rebuilding the new table's index from scratch.
This worked fine, for 2 weeks. Now, suddenly, the message is showing up,
again. The table presently contains over 900 records, however the index
wants to jump back to somewhere around 700. I "fixed" the problem, the
same way, again, but would like to know what may be causing this, and how I
can prevent it from continuing to happen. Thanks.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Sounds like a corrupted DB. Have you tried Compact & Repair?

On the corruption part: Does each user have their own copy of the front
end on their PC? If more than one user is using the same front-end
corruption of the data can occur.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQh9+k4echKqOuFEgEQK2+wCgizw2d/3Tmy5CBc+DSOwwbbyLb5MAmwfq
TAwMj49vTyVpaMSr33cXVEjN
=chtr
-----END PGP SIGNATURE-----

[Bari Allen via AccessMonster.com]

Thank you for your reply, this has already happened, again. No--they do
not have their own front-ends, as only one person enters data, and there is
only one other person that accesses it to read the information. As for
compact/repair, yes I've tried that, and it hasn't solved the problem. The
database already has the front/back split, so I'll try putting it on their
individual machines & see if it makes a difference. I have no other ideas
as to why this keeps happening, all of a sudden, over the last month for a
program that's been in use for many years. If anyone else can offer an
opinion, I'm willing to hear. Thank you.

--
Message posted via http://www.accessmonster.com

[phhathome]

Hi Bari,

This is a bug in MS Access recognised by MS and not helped by Jet SP8.
There are a couple of articles in the KB on this but no really
satisfactory work-around and the cause is not well defined (an
"improperly seeded" column!!). There are DDL queries you can use e.g.

ALTER TABLE TableName ALTER COLUMN AutoNumFieldName COUNTER(iMaxID,1);

to "reseed" the autonumber column but you have to remove the table
from all relationships for this to work. This was no good in my case.
The only solution I can see is to change the field to number and then
write your own auto-number function - not very good I know.

Hope this helps,

Peter

"Bari Allen via AccessMonster.com" <fo***@AccessMonster.com> wrote in message news:<0d******************************@AccessMonst er.com>...

Thank you for your reply, this has already happened, again. No--they do
not have their own front-ends, as only one person enters data, and there is
only one other person that accesses it to read the information. As for
compact/repair, yes I've tried that, and it hasn't solved the problem. The
database already has the front/back split, so I'll try putting it on their
individual machines & see if it makes a difference. I have no other ideas
as to why this keeps happening, all of a sudden, over the last month for a
program that's been in use for many years. If anyone else can offer an
opinion, I'm willing to hear. Thank you.