Executing a pass-through SQL Server query from Access VBA
I have a UPDATE
pass through a query saved in Access 2007. When I double click on a pass through a query, it succeeds. How can I get this request to run from VBA? I would like it to start when my splash screen loads.
I am currently using the following code:
CurrentDb.Execute "Q_UPDATE_PASSTHROUGH", dbSQLPassThrough
But I get the following message:
The walkthrough query contains all the connection information and I have verified the SQL syntax is correct by running it multiple times, so not sure what I am missing in the VBA call.
source to share
I confirm that the QueryDef method Execute
is the recommended way to achieve your goal.
CurrentDb.QueryDefs("Q_UPDATE_PASSTHROUGH").Execute
However, I can point out that in a similar case with Access 2010, using dbSQLPassThrough
for parameter Options
caused Run-time error '3001': Invalid Argument
.
source to share
I recently faced the same problem. While the above method Execute
works in most cases, some people (myself included) are experiencing Run-time error '3001': Invalid Argument
when using the dbSQLPassThrough parameter. This was also mentioned in the answer above and happens in even the most basic SQL statements.
For those who have the same problem, I recommend using the method OpenQuery
as an alternative.
Valid replacement for the following code
CurrentDb.QueryDefs("Q_UPDATE_PASSTHROUGH").Execute
would
DoCmd.OpenQuery "Q_UPDATE_PASSTHROUGH"
I know this thread is 4 years old, however searching for a solution for the not working method Execute
on Google brings you directly to this thread, so I thought it would be helpful to add an alternative solution that would solve this problem for me.
source to share