Stop or ignore SQL Server information message in Access pass through query

Access 2016 connected to SQL Server 2016 via ODBC DSN.
I have a macro that runs a series of Access and pass-through queries, where one of the pass-through queries appends a set of records from one SQL table to another SQL table.
One of the fields in the destination table is deliberately set with an index that does not allow duplicates.  The source table correctly has duplicates, which is fine, as I have set the index in SQL to ignore duplicates so that the pass-through query works and is not terminated.  The end result is that I get one instance of each record in the destination table, which is what I want for some interim processing.  The problem is:

Even though I have set warnings in the macro to No as the first macro action, which deals with all the action messages from all the queries, this does not stop an information message coming from SQL about these duplicates, even though it has successfully run the query and ignored them, which effectively stops my macro.  How can I ignore this message?  If I run it as a stored procedure in SQL it simply says completed, duplicates ignored.  I am not normally aloud into the SQL side so needs to be solved in Access.
Paul BarrettMDAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Well, without out knowing your exact message, we need to guess: Do you have an index violation on SQL Server?

You need to GROUP BY/DISTINCT your source row set before inserting it into your indexed table.

Set Warnings to No affects only Access warnings. Not SQL Server errors.
Paul BarrettMDAuthor Commented:
This is the thing, it is not an SQL error, it is doing what it should do.  In SQL stored procedure it finishes with successful, duplicates ignored.

But when run as a pass-through query I get the attached error message in Access.  It suggest ODBC error which is a red herring as I can see by looking in the table it has done it successfully.The error message in Access
Fabrice LambertConsultingCommented:
Guess the stored procedure handle this case, so run it.

The following article explain how:
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

ste5anSenior DeveloperCommented:
The problem is your table and its unique index. This warning message is sent, when you insert duplicate rows in a table having an UNIQUE INDEX with IGNORE_DUP_KEY set to ON. Thus your UNIQUE INDEX does not ensure your wanted situation.
Mark EdwardsChief Technology OfficerCommented:
If you were doing this in VBA code, I'd suggest putting a code line "On Error Resume Next" before running the pass-thru query and checking for DAO.Errors after that line so you could selectively handle those errors you are expecting and any ones you aren't.

However, how to do that with an Access Macro, I'm not sure.  I know Macros have an "On Error" and "Next" command and the ability to run an error handler, but I've never used it.

Perhaps there's another expert who is more familiar with how to do that....
Fabrice LambertConsultingCommented:
I'd suggest putting a code line "On Error Resume Next"
Waaah !!!
No no no !!
Never ever ignore errors, write an error handler and react accordingly.
Sub mySub()
On Error Goto ErrorHandler
    '// code that can fail here
Exit Sub
    Select Case Err.Number
    Case XXX
        '// Code handling error N°XXX here
    End Select
End Sub

Open in new window

Mark EdwardsChief Technology OfficerCommented:
In this case, the purpose of the "On Error Resume Next" in VBA code is to setup what's know as an "In-Line Error Trap" that allows the code line to run and fail, then check for DAO errors immediately after with :
If DAO.Errors.Count>0 then
    'put error handling code here to obtain desired results....
End if
After the errors are handled, you can reset to your generic unexpected error handler "On Error GoTo Err_Handler" (as an example).
I use in-line error trapping whenever I know that I have a good chance of throwing a known error that I want ignored.

But you can also write an error handler to jump to, with appropriate "GoTo"'s  or "Resume"'s  if you prefer to go that route.  Seems a bit overkill with a higher chance of screwing things up, though.

However, the author s not asking for a list of personal preferences of error handling techniques, but a way to handle an expected error generated by a Macro step without throwing up an error message.  While the "OnError" and "Next" macro command step would allow the query step to fail without a pop-up message, it wouldn't handle an unexpected error - just allow the macro to keep executing.

I'm hoping there is another expert out there that already knows how to do that.  Since our use of Macro objects is very limited in scope and number, I've never had to worry about that before, but we have taken it on as a challenge and will post the results here as soon as we have an answer, while everyone else tries to  get the author to re-write his application for various personal reasons.

Wait for my post Paul.....
Mark EdwardsChief Technology OfficerCommented:
Ok Paul, here's what I've got:

Take a look at this:

You can have your failing macro action (query) trigger a submacro error handler, or you can set "OnError" and "Next" macro action before running the query, and after the query macro action fails, intercept the error with an "If" action:
If [MacroError].[Number]<>ExpectedErrorNumberHere Then
and let the processing of this macro action handle any unexpected error.  If the error is the one you are expecting, then it moves on to the next macro action.

Hope this helps, even if it is not too detailed....

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Paul BarrettMDAuthor Commented:
Thank you Mark, your solution works in the problem that I posed as it solves it within the running macro.  I cannot believe I have not come across the On Error macro action before, possibly because I would never wish to ignore an error!  It always suggests putting the On Error as the first macro action but I have taken a different view.  I have put it just before the pass through query and then just after the pass through query I have added an action that checks the validity of what that step should have done and if this check fails it runs another macro that backs out of the step gracefully.  After that I return the macro back to normal with another On Error macro step.  That way errors are checked for macro actions before that step, ignored for the pass through query in question, but then the next step checks the validity of that action, then the macro is returned to normal On Error behaviour for the further couple of actions left to run.
ste5anSenior DeveloperCommented:
Just to clarify it: You want duplicates in your destination table?

In this case drop the UNIQUE index and create a regular index instead. Better semantics.
Fabrice LambertConsultingCommented:
Well, mixing error handling in a function body isn't what I call a good practice, as testing errors isn't the function's responsibility.

if this check fails it runs another macro that backs out of the step gracefully
Isn't it what transactions are for ?
Mark EdwardsChief Technology OfficerCommented:
Paul:  Glad it works for you.  Don't forget to add the "ClearMacroError" step after you have handled the error.  I don't know if setting another error handler step clears the MacroError, but just to be on the safe side....
Paul BarrettMDAuthor Commented:
Thanks, I will have a look at  this element and set accordingly.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.