troubleshooting Question
Stop or ignore SQL Server information message in Access pass through query
Paul Barrett
asked on
asked on
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.
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.
Learn from the best
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.