Run-time error '3022' - Suppress it

[Moxioron]

Hello.

I am using the following code to run an append query on another table

Dim db As DAO.Database
Set db = DBEngine.Workspaces(0).OpenDatabase("R:\DEPT-BR\CONSUMER LENDING\Marketing Campaigns\2015\2015 Auto Prequal Campaigns\August 31 Mailing\Auto Loan Prequalification - August 31 2015.accdb")
db.Execute "qry_08312015 Auto Loan Prequal Results - Append", dbFailOnError
Set db = Nothing

It works great .... except when it finds dupes. I have two fields in the table the data is appending to as the primary keys to obviously prevent dupes, but some of the records in the append are not duplicates.

So my ?s

1.) Does this Run-time error prevent all records from being appended or just the dupes?
2.) How can I suppress the error? I tried DoCmd.SetWarnings False to no avail.

Thanks.

 

[HiTechCoach]

TIP: When posing code use the code tag. It is the button with the #

Dim db As DAO.Database

Set db = DBEngine.Workspaces(0).OpenDatabase("R:\DEPT-BR\CONSUMER LENDING\Marketing Campaigns\2015\2015 Auto Prequal Campaigns\August 31 Mailing\Auto Loan Prequalification - August 31 2015.accdb")

db.Execute "qry_08312015 Auto Loan Prequal Results - Append", dbFailOnError

Set db = Nothing

1.) Does this Run-time error prevent all records from being appended or just the dupes?

No records are appended if any error occurs.

dbFailOnError Rolls back updates if an error occurs (Microsoft Access workspaces only).

from MSDB: https://msdn.microsoft.com/en-us/library/office/ff197654.aspx

2.) How can I suppress the error? I tried DoCmd.SetWarnings False to no avail.

Using

DoCmd.SetWarnings False

only applied to the CurrentDB (defalut) worksapce not the db workspace you created.

IIRC, you can use this:

db.DoCmd.SetWarnings False

Might try this:

Dim db As DAO.Database

Set db = DBEngine.Workspaces(0).OpenDatabase("R:\DEPT-BR\CONSUMER  LENDING\Marketing Campaigns\2015\2015 Auto Prequal Campaigns\August 31  Mailing\Auto Loan Prequalification - August 31 2015.accdb")

db.DoCmd.SetWarnings False
db.Docmd.RunSQL "qry_08312015 Auto Loan Prequal Results - Append"
debug.print "records appened: " & db.[B][URL="https://msdn.microsoft.com/en-us/library/office/ff192040.aspx"]RecordsAffected[/URL][/B]
db.DoCmd.SetWarnings True

Set db = Nothing

 

[Micron]

Moxioron: I think you will find that even if you can suppress the error message, your query will append no records when it encounters a duplicate primary value. You will not even know it failed unless you trap for the specific error. Assuming you cannot edit the query to remove the duplicates, or the table to allow them, you might solve the issue by creating a query that returns a unique recordset using either the DISTINCT or DISTINCT ROW predicate and have your append query use this as a table. If the record containing the duplicate is exactly the same, this should not be difficult. However, if any of the fields contain different data yet the PK is a duplicate, you will probably have to follow the first operation with an update query to get the different values or figure a way to get the record you want out of the ones with the duplicate PK.