Execute SQL Server Pass-Through Query From Access VBA

Question

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.

HansUp · Accepted Answer · 2014-12-30 18:12:10Z

6

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

answered Dec 30, 2014 at 18:12

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Xalion · Accepted Answer · 2019-04-10 08:29:58Z

4

I recently ran into the same problem. While the above mentioned Execute method is working for most cases, some people (me included) experiencing a Run-time error '3001': Invalid Argument when using the parameter dbSQLPassThrough. This was also addressed in the answer above me and happens even in the simplest SQL-statements.

For those who are having the same problem, I recommend using the OpenQuery method as alternative.

A valid substitution for the following code

CurrentDb.QueryDefs("Q_UPDATE_PASSTHROUGH").Execute

would be

DoCmd.OpenQuery "Q_UPDATE_PASSTHROUGH"

I know this thread is 4 years old, however, searching for a solution for the not working Execute method on Google brings you directly to this thread which is why I thought it would be useful to add an alternative solution which solved this problem for me.

edited Apr 10, 2019 at 8:29

answered Apr 10, 2019 at 8:24

Xalion

951 silver badge8 bronze badges

3 Comments

Andre Over a year ago

experiencing a Run-time error '3001': Invalid Argument when using the parameter dbSQLPassThrough. -- Does it also not work if you omit the parameter dbSQLPassThrough?

Xalion Over a year ago

The Execute statement works if I omit the dbSQLPassThrough parameter, but from my still very limited understanding of Access VBA I thought it is necessary to use it because otherwise the heavy SQL-statement be executed on my local machine instead on the SQL Server.

Andre Over a year ago

No. If you .Execute a saved query that is a PT query (i.e. has a Connect string), you don't need the parameter - Access knows it's a PT query and will not try to run it with the Access DB engine. In most cases it wouldn't work anyway, since the SQL dialects are different between Access and the server DB.

Manos Anastasiadis · Accepted Answer · 2016-05-20 08:23:47Z

1

I confirm that the QueryDef's Execute method 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 the Options parameter caused a Run-time error '3001': Invalid Argument.

answered May 20, 2016 at 8:23

Manos Anastasiadis

1161 silver badge6 bronze badges

Collectives™ on Stack Overflow

Execute SQL Server Pass-Through Query From Access VBA

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related