autonumber key violation on insert

[oraustin@hotmail.com]

Hi again.
I'm running Access 97 and in code, using docmd.runsql, I insert new
rows into a table. The destination table's key field is an autonumber.
99% of the time the code works fine but sometimes it won't add the
record/s due to a key violation.

Is this a known bug in Access, is there some workaround?

Thanks
Oliver

[Steve Jorgensen]

Re: autonumber key violation on insert

On 16 Jun 2005 05:22:13 -0700, oraustin@hotmai l.com wrote:
[color=blue]
>Hi again.
>I'm running Access 97 and in code, using docmd.runsql, I insert new
>rows into a table. The destination table's key field is an autonumber.
>99% of the time the code works fine but sometimes it won't add the
>record/s due to a key violation.
>
>Is this a known bug in Access, is there some workaround?
>
>Thanks
>Oliver[/color]

It is a bug. On occasion, autonumber sequences can become corrupted. Running
a repair/compact on the database fixes the problems.

[Tom van Stiphout]

Re: autonumber key violation on insert

On 16 Jun 2005 05:22:13 -0700, oraustin@hotmai l.com wrote:

Check all unique indexes on that table (design view > View > Indexes).
The data to be inserted must be violating one of them. You can write a
query to find the record(s).

-Tom.

[color=blue]
>Hi again.
>I'm running Access 97 and in code, using docmd.runsql, I insert new
>rows into a table. The destination table's key field is an autonumber.
>99% of the time the code works fine but sometimes it won't add the
>record/s due to a key violation.
>
>Is this a known bug in Access, is there some workaround?
>
>Thanks
>Oliver[/color]

[oraustin@hotmail.com]

Re: autonumber key violation on insert

I guess index means primary key? There's only one - the autonumber - I
don't give it a value on the insert (obviously). If it's a bug - which
it seems to be and Steve agrees - then is there some way to minimise it
happening? I need the system to be robust.
Thanks Oliver