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

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.

Guess the stored procedure handle this case, so run it.

The following article explain how:
https://accessexperts.com/blog/2011/07/29/sql-server-stored-procedure-guide-for-microsoft-access-part-1/

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.

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....

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
ErrorHandler:
    Select Case Err.Number
    Case XXX
        '// Code handling error N°XXX here
    End Select
End Sub

Select all Open in new window

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.....

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.

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 ?

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....