Execute SQL Server Pass-Through Query From Access VBA

sql ms-access vba pass-through
mntyguy picture mntyguy · Dec 30, 2014 · Viewed 12.9k times · Source

I have an UPDATE pass through query saved in Access 2007. When I double-click on the pass through query it runs successfully. How can I get this query to run from VBA? I'd like it to run when my "splash screen" loads.

I'm currently using the following code:

CurrentDb.Execute "Q_UPDATE_PASSTHROUGH", dbSQLPassThrough

But I get the following message:

enter image description here

The pass-through query contains all the connection information and I've confirmed the SQL syntax is correct by running it multiple times, so not sure what I'm missing in my VBA call.

Answer

HansUp picture HansUp · Dec 30, 2014

Use the QueryDef's Execute method:

CurrentDb.QueryDefs("Q_UPDATE_PASSTHROUGH").Execute

I don't think you should need to explicitly include the dbSQLPassThrough option here, but you can try like this if you want it:

CurrentDb.QueryDefs("Q_UPDATE_PASSTHROUGH").Execute dbSQLPassThrough

Related questions

How to insert values into the database table using VBA in MS access

I've started to use access recently. I am trying to insert a few rows into the database; however, I am stuck as it is throwing an error: Too few parameters. I have a table test with only one column in …

Read More
MS Access - execute a saved query by name in VBA

How do I execute a saved query in MS Access 2007 in VBA? I do not want to copy and paste the SQL into VBA. I rather just execute the name of the query. This doesn't work ... VBA can't find the …

Read More
Using Excel VBA to export data to MS Access table

I am currently using following code to export data from worksheet to MS Access database, the code is looping through each row and insert data to MS Access Table. Public Sub TransData() Application.ScreenUpdating = False Application.EnableAnimations = False Application.EnableEvents = …

Read More