Does Ms Access Suppress Primary Key Violations on Inserts

Does MS Access suppress primary key violations on Inserts?

After HansUp pointing me in the direction of checking for SetWarnings = false. I found it buried in my code which is why there was no warning message about the records not being inserted due to primary key violations.

A word of caution would be to make sure you want these messages suppressed.

Insert into Linked table in access without the primary key

Notice that the very last item in the column list of the INSERT INTO clause is ProductID. So, you are trying to insert a new row with an existing Primary Key value, and that won't work. As a simplified example,

INSERT INTO tblProducts (ProductDescription, ProductID)
SELECT tblProducts.ProductDescription, tblProducts.ProductID
FROM tblProducts
WHERE tblProducts.ProductID=1

will fail with a primary key violation. You need to remove ProductID from both the INSERT INTO and SELECT clauses, and only use it in the WHERE clause:

INSERT INTO tblProducts (ProductDescription)
SELECT tblProducts.ProductDescription
FROM tblProducts
WHERE tblProducts.ProductID=1

Primary key violation triggers warning, but does not trigger error handler

You need to use the Database.Execute() method, passing the dbFailOnError option flag which will rollback changes and raise a runtime error if the query fails for whatever reason.

Currentdb().Execute strSQL, dbFailOnError

Fail to Append due to Key Violations

Inspect the INSERT statement you're asking Access to execute.

sql = "INSERT INTO tblHistory (BoxType, QtyChange, NewQty) VALUES ('&BoxType&','&change&','&newqty&')"
Debug.Print sql

Run the code and examine the INSERT statement in the Immediate window. You can go there with Ctrl+g.

And you can copy the statement text and paste it into SQL View of a new Access query for testing, which should help you diagnose the problem. Paste the statement text into your question if you need more help from us.

Also consider converting the INSERT to a parameter query. You can use that named query in your VBA code and supply the parameter values at run time.

Assuming you created and tested a parameter query similar to the one below, you named it qryHistoryAppend, and it includes pBoxType, pQtyChange, and pNewQty as the named parameters, you can open that query (QueryDef object) in VBA, supply the parameter values and execute it.

Const cstrQuery As String = "qryHistoryAppend"
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs(cstrQuery)
qdf.Parameters("pBoxType") = Forms!formMain!txtBoxType
qdf.Parameters("pQtyChange") = Forms!formMain!txtQtyChange
qdf.Parameters("pNewQty") = Forms!formMain!txtQty
qdf.Execute dbFailOnError ' <- always use Execute instead of DoCmd.RunSQL

Note if that code is contained in formMain, you can reference the text boxes like Me!txtBoxType instead of Forms!formMain!txtBoxType

My idea for qryHistoryAppend looks like this. Adjust as needed.

INSERT INTO tblHistory (BoxType, QtyChange, NewQty)
VALUES (pBoxType, pQtyChange, pNewQty);

Access didn't add 1 record(s) to the table due to key violation. What is wrong here?

I've found the problem! Before writing queries I've created tables in Exel

And what I was doing, I was doing copy/paste values from Exel to my queries.
But when I tried to write values manually into the queries, it started to work!

I think when I was copying values from Exel, it was copying something else too, SO NEVER EVER COPY VALUES FROM EXEL TO YOUR QUERIES.

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

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.

Inserting a value into a primary key field in Access

I'm not sure if I'm fully understanding the issue here, but there may be a couple of reasons why this isn't working. The biggest thing is that any sort of primary key column has to be unique for every record in your lookup table. Like you mentioned above, 0 is a pretty common value for 'unknown' so I think you're on the right track.

Does 0 or 9999999 already exist in [Reading]? If so, that could be one explanation. When you wiped the table out before, did you completely drop and recreate the table or just truncate it? Depending on how the table was set up, some databases will 'remember' all of the keys it used in the past if you simply deleted all of the data in that table and re-inserted it rather thank dropping and recreating it (that is, if I had 100 records in a table and then truncated it (or deleted those records), the next time I insert a record into that table it'll still start at 101 as its default PK value).

One thing you could do is to drop and recreate the table and set it up so that the primary key is generated by the database itself if it isn't already (aka an 'identity' type of column) and ensure that it starts at 0. Once you do that, the first record you will want to insert is your 'unknown' value (0) like so where you let the database itself handle what the ReadingID will be:

INSERT INTO [Reading] ([EntryFK], [Reading], [NotTrueReading]) VALUES (0, "", FALSE)

Then insert the rest of your data. If the other table looking up to [Reading] has a null value in the FK column, then you can always join back to [Reading] on coalesce(fk_ReadingID,0) = Reading.ReadingID.

Hope that helps in some capacity.

---

Related Topics