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)

Iq

To make the link readable you should use the SO markup not html to show links etc. – Implore

I don't know about Access in particular, but this can happen in some databases if you have a trigger that inserts an additional row with each insert with an explicit value for the auto increment key. Investigate such possibilities, and post the structure of the table together with anything relevant, and select the biggest primary key before insertion and after insertion and paste them here too. – Wantage

What happens on the third/fourth executions? Is it only the first execution that fails or is it every second one? – Chiton

@Chiton - Only the first time. – Iq

I'm grasping at straws, but your autonumber might be getting messed up somewhere else. Right before the insert, try turning autonumbering off and then on again to reset the counter. – Chiton

@Richard aka cyberkiwi: "Access has no triggers" -- Access 2010 introduced data macros to the database engine which are analogous to triggers e,g, see the Access Team blog (blogs.office.com/b/microsoft-access/archive/2009/08/13/…) – Puffy

@Puffy - not relevant when the tag is 2007... – Antipodal

@Richard aka cyberkiwi: If you'd said that "Access2007 has no triggers" then I wouldn't have accused you of propagating ignorance ;) – Puffy

What are the indexes on the table? – Birdwell

Do you have your ID field set as the primary key? It looks like the violation is occurring because the Now() timestamp is identical because of the speed with which the query is being executed. This should only occur though if you have your accessed_on field set as the primary key but I wonder if it could also occur if you have no primary key specified. – Childbed

@Childbed - ID field is the primary key. @Birdwell - I have not created any indexes manually, so I think that the PK is the only indexed field in the table. Not sure, though as I have never worked with indexes. – Iq

I'd doublecheck the indexes, but also if any fields are marked required. – Birdwell

From a deleted comment, which I am not sure if you read, but have you done a Compact and Repair? It does solve a lot of Access issues. You can also go down the route of decompiling if you had any macros. – Antipodal

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.

Lophobranch

TimeStamps are generally only relevant for editing data in bound forms in Access, since that allows Access to refresh the displayed data if another user updates. PK and Timestamp are essential for it to work smoothly, and I add a timestamp field in every SQL Server table just as a matter of course. And the SQL Server Migration Assistant for Access does the same thing, i.e., when it upsizes your Access data tables, it adds a timestamp field to all of them in the SQL Server version. – Birdwell

Timestamps are relevant to key generation as well. Should not be the case, yet it did force Access to stop generating the key violation error. In the experience I related in my answer, it had nothing to do with a bound form. – Lophobranch

I never really got the chance to test the time stamp fix, but I did run into this issue later in another DB. Accepting your answer because you mentioned access 'getting its wires crossed'. Seems to be exactly what's occurring here. Compacting and repairing the DB works to fix this issue as well. – Iq

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

Selfpossession

Answer 3

Is the value Now changing between attempts so that there is now no longer a duplicate key error?

Sprinkle

Answer 4

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

Can you just check this out with accessed_on datatype and Now datatype

Change the value type of DateTime to String while inserting that will be good.
Do let me know if this works for you.

Thanks
rAfee

Lemuellemuela

What's the point of the recommendation of changing the date to string for the insert? That means you don't get validation of the values. Also, you're passing the Now() function to Jet/ACE to process. I'm not entirely sure how Jet/ACE treats that if the query takes a long time. Does it get evaluated once so that all the records get the same date/time value? Or does it get evaluated row-by-row so that they aren't all the same? I'd suggest getting the value and concatenating the literal value to send to Jet/ACE for insertion. – Birdwell

In any case, the accessed_on column in the insert statement is unnecessary because there's a default in the database. – Selfpossession

Answer 5

I believe Jet/ACE will not understand the NOW() method.

And i worked with ACE version, the syntax could not work. Need to find the other way for direct implementing the syntax.

Lemuellemuela

@Selfpossession points out in a comment that Now() is the default value on the field, so that field can be completely ommitted from the SQL statement. – Birdwell

Answer 6

I know long time ago I had a similuar issue. In my cases I was getting the same error but I didn't have any unique indexes in the table. I finally solved it by reparing and compacting the database.

Gloriane