473,386 Members | 1,698 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,386 software developers and data experts.

autonumber key violation on insert

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

Nov 13 '05 #1
3 2691
On 16 Jun 2005 05:22:13 -0700, or******@hotmail.com wrote:
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


It is a bug. On occasion, autonumber sequences can become corrupted. Running
a repair/compact on the database fixes the problems.
Nov 13 '05 #2
On 16 Jun 2005 05:22:13 -0700, or******@hotmail.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.

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


Nov 13 '05 #3
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

Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: J Smith | last post by:
After doing some googling through the lists and such, I wasn't able to arrive at a solution for a problem I've run into recently. I've seen mention of similar symptoms, but my case seems different....
3
by: Ilan Sebba | last post by:
I have a 'supertype' table with only one field: autonumber. Call this table the 'parent' table. There are two subtypes, 'androids' and 'martians'. Martian have only one thing in common: they give...
8
by: bigbinc | last post by:
I am in autonumber hell, it is important for me to get the AutoNumber even in case of previous deleted records, and I cant get the value through a 'test' insert and then delete method. There is no...
8
by: petebeatty | last post by:
I have created a SQL string the properly inserts a record in the table. However, the insert does not occur at the end of the table. Instead it inserts a record after the last record that I viewed....
11
by: Alan Mailer | last post by:
A project I'm working on is going to use VB6 as a front end. The back end is going to be pre-existing MS Access 2002 database tables which already have records in them *but do not have any...
7
by: pltmcs | last post by:
I am trying to generate some records from one table into another. The problem is that the new table has an autonumber field (PID). The PID is part of the primary key since it is possible to have...
4
by: Lyn | last post by:
Hi, Is there an "easy" way to write a full record (all fields) using "INSERT INTO..." into a table which has an AutoNumber field? Normally, to write a full new record I would use: INSERT INTO...
1
by: mark mestrom | last post by:
Hi all, i have a problem. In my access-application i am trying to insert a record in a linked table with resides in a sqlserver 2000 database. However, the primary key in that table is an INT with...
6
by: ashes | last post by:
Hi, I am creating an ecommerce website using Microsoft Visual Studio, VB.Net and MS Access 2003. I am new to VB.Net When someone wants to register on the website, they fill out a form and the...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.