Does MS Access provide primary key violations on inserts?

Question

I am rewriting the MS Access database to an SQL server and have discovered a strange problem in Access that I hope someone can help.

I have a table, name it "Home" with the Primary Key in the Account, which is indexed and does not allow duplication. It seems simple enough, but my problem arises when data is inserted.

My INSERT request (number of fields is limited for brevity)

INSERT INTO Main (Account, SentDate, Amount) SELECT C.Account, C.SentDate, C.Amount FROM (CALLS C LEFT JOIN Bals B ON C.Account = B.ACCT_ID) LEFT JOIN AggAnt A ON C.Account = A.Account 

The problem is that if I run the SELECT part of my query, I get 2365 records, but when I run INSERT , I get 2364 records. So I did some verification, and I found that one account is duplicated, the difference between the entries is SentDate and Amount. But Access only inserts one of the entries and does not throw any error messages or anything else. There is nothing in the request that indicates the selection of the most recent date, etc.

Sample data:

 Account SentDate Amount 12345678 8/1/2011 123.00 23456789 8/1/2011 45678.00 34567890 8/1/2011 7850.00 45678912 8/1/2011 635.00 45678912 5/1/2011 982.00 56789123 8/1/2011 2639.00 

In the sample, I have one account that is duplicated 45678912, when I start my INSERT, I have no errors, and I get the record from 8/1/2011.

Why doesn't Access throw an error when it violates PK on the table? Is there any quirk in Access to select one entry and just skip the other?

I am completely concerned about this issue, so any help would be great.

Answer 1

After HansUp directs me in the direction of checking SetWarnings = false. I found it in my code, so there was no warning message that the records were not inserted due to primary key violations.

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

Answer 2

How do you execute the request? If you are using DoCmd.RunSQL , switch to using the .Execute method of the .Execute database object and use dbFailOnError .

 Dim db As DAO.Database Dim strInsert As String strInsert = "your insert statement" Set db = CurrentDb db.Execute strInsert, dbFailOnError Set db = Nothing 

Change If Main is an ODBC reference to a SQL Server table, I would look at the Error Collection (DAO) after db.Execute strInsert, dbFailOnError

Answer 3

Is there any quirk in Access for [updating] one entry and just skip the other?

Yes, you can control this behavior at the engine level (also at the record set level when using OLE DB).

For OLE DB (e.g. ADO), Jet OLEDB:Global Partial Bulk Ops setup:

defines the behavior of the Jet database engine when SQL DML bulk operations fail. If set to provide partial completion of an operation, inconsistent changes may occur, as operations on some records may be successful, while others may fail. If set to partial completion of bulk operations, all changes are rolled back if a single error occurs. OLEDB: Global Partial Bulk Operations property can be redefined based on Recordset by setting the Jet OLEDB: Partial Bulk Ops property in the Recordset object's Collection Properties .

Please note that the default value is to prevent partial completion of bulk operations.