Why does MS Access 2007 not allow a row insert, but then allow it on the next insert attempt?

Question

My insert statement is:

INSERT INTO myTable (inst_id,user_id,app_id,type,accessed_on)
VALUES (3264,2580,'MyApp','Renew',Now);

...where all of the values are formatted correctly. The table has the above fields and one other, a long int auto-increment key field. The foreign keys are 'inst_id', 'user_id', and 'app_id'.

I am getting this error from Access:

...and the following error from VS 2005 when it errors out:

System.Data.OleDb.OleDbException: The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

When making this insert query I can look into the database and see that the each of the foreign key values exist in their respective tables and have been for months (for the particular example I am using). These fields are also set so that I can have duplicates, so that is not the issue. Calls of this nature in other tables works great. I do not need to supply the auto-increment key value in the insert query, it adds it for me automatically (like it should).

The weird thing is that if I do this in my code:

try
{
    //Execute the query here...
}
catch
{
    //Execute the same query again
}

...or if I just try and execute this within Access twice, it works.

Has anyone encountered this before? Again, this type of insert works for other tables, all foreign keys are present in their respective tables, the primary key of this table is set as 'Auto-increment', and all fields (other than the primary key field of course) are set to allow duplicates.

Any ideas?

EDIT: Largest key before inserting: 343085. Largest key after inserting: 343086. The format is:

id: AutoNumber (Field Size=Long Interger, New Values=Increment, Indexed=Yes - No Duplicates)

inst_id: Number (Field Size=Long Interger, Required=Yes, Indexed=Yes - Duplicates OK)

user_id: Number (Field Size=Long Interger, Required=Yes, Indexed=Yes - Duplicates OK)

app_id: Text (Field Size=255, Required=Yes, Indexed=Yes - Duplicates OK)

type: Text (Field Size=50, Required=Yes, Indexed=No)

accessed_on: Date/Time (Default Value=Now(), Required=Yes, Indexed=No)

Answer 1

Going by some old memory here...

Try putting a timestamp field in your table.

I can't remember exactly why that works -- something to do with Access having difficulty identifying records / maybe some kind of locking or indexing quirk. I did some research on that several years ago when it happened to one of my tables.

The key violation the error refers to isn't a missing key in another table, it's a duplicate key in the same table. Sometimes, Access gets it's wires crossed and thinks that the key it's assigning to the new record is already assigned to another record in the table. I don't know what causes that to happen. But by putting a timestamp field in the table, it causes Access to think differently.

It's a frustrating fix, because I don't know why it works. And now I have an otherwise useless timestamp field in my table. But so be it.

Answer 2

MS-Access has been known to barf up spurious errors that have nothing to do with the problem they report. It wouldn't hurt to surround the column called "type" with brackets, [type].

http://office.microsoft.com/en-us/access-help/access-2007-reserved-words-and-symbols-HA010030643.aspx#_Toc272229038