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