Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Does MS Access suppress primary key violations on Inserts?

I am in the process of re-writing an MS Access database to SQL server and have found an strange issue in Access that I am hoping someone can help with.

I have a table let's call it 'Main' with a Primary Key on the Account that is indexed and doesn't allow for duplicates. Seems simple enough but my issue is occurring when data is getting Inserted.

My INSERT query is (the number of fields have been 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 issue is this, if I run the SELECT portion of my query I get 2365 records but when I run the INSERT I get 2364 records. So I did some checking and I found one Account is duplicated the difference between the records is the SentDate and the Amount. But Access is inserting only one of the records and not throwing any kind of error message or anything. There is nothing in the query that says select 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 run my INSERT, I get no errors and I get the record from 8/1/2011.

Why is Access not throwing an error when this violates the PK on the table? Is there some quirk in Access to select one record and just skip the other?

I am totally stumped by this issue so any help would be great.

like image 534
Taryn Avatar asked Aug 18 '11 23:08

Taryn


People also ask

What is key violation in access append query?

Key violations You may be trying to append data into one or more fields that are part of the table's primary key, such as the ID field. Check the design of the destination table to see if the primary key (or any index) has the No Duplicates property set to Yes.

What is a primary key violation?

A Primary Key violation occur when there is an attempt to insert a value into a database table, but the entry already exists.

What is a primary key constraint in SQL?

The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

How delete primary key constraint in SQL?

To delete a primary key constraint using Table Designer. In Object Explorer, right-click the table with the primary key, and select Design. In the table grid, right-click the row with the primary key and choose Remove Primary Key to toggle the setting from on to off.


2 Answers

How are you running the query? If you're using DoCmd.RunSQL, switch to using the .Execute method of a DAO 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

Edit: If Main is an ODBC link to a SQL Server table, I would examine the Errors Collection (DAO) after db.Execute strInsert, dbFailOnError

like image 123
HansUp Avatar answered Sep 21 '22 07:09

HansUp


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.

like image 23
Taryn Avatar answered Sep 22 '22 07:09

Taryn