Pass through query how to run in VB code.

  • Thread starter burningthemidniteoil
  • Start date
B

burningthemidniteoil

Hi I have a pass through query that i have defined in access, and
works fine.

How do you run it in code?
I get the error 'Cannot execute a select query.'

Set db = OpenDatabase(sdb)

With db
sSQL = db.QueryDefs(qry).SQL
If db.QueryDefs(qry).Type = dbQSQLPassThrough Then
db.QueryDefs(qry).Execute ' ??? How to run
what params??

Else

.Execute sSQL, dbFailOnError ' normal access query runs ok
End If

I have also tried .Execute sSQL, dbSQLPassThrough - dosen't work..and


any ideas..
 
D

Dirk Goldgar

Hi I have a pass through query that i have defined in access, and
works fine.

How do you run it in code?
I get the error 'Cannot execute a select query.'

Set db = OpenDatabase(sdb)

With db
sSQL = db.QueryDefs(qry).SQL
If db.QueryDefs(qry).Type = dbQSQLPassThrough Then
db.QueryDefs(qry).Execute ' ??? How to run
what params??

Else

.Execute sSQL, dbFailOnError ' normal access query runs ok
End If

I have also tried .Execute sSQL, dbSQLPassThrough - dosen't work..and


any ideas..


The Execute method only applies to action queries. I gather from the error
that this is a SELECT query. What do you want to do with this query? Do
you want to open it to view its results in a datasheet? If so, you'd use

DoCmd.OpenQuery qry

Do you want to return a recordset so as to process those records in code?
Then you'd do something like this:

Dim rs As DAO.Recordset

Set rs = qry.OpenRecordset

At the moment, I don't see where the pass-through nature of the query comes
into it.
 
B

burningthemidniteoil

No its not a select, i'm deleting from a legacy odbc linked table
(ingres) using a pass thru
the query works from access ok but how do I get it to work from code..
 
A

Albert D. Kallal

Is the query is saved already?

Just go:

currentdb.Querydefs("name of saved pass-though query").execute

In fact, in the case of a strode procedure that needs a parameter, I build
ONE pass-tough query, and then though out the application, then I can use
that to execute "any" stored procedure, "any" sql command, and I not
restricted to just the one the pass-through query that the query object is
based on.

You can thus use:

Dim qdfPass As DAO.QueryDef
Dim rst As DAO.Recordset


Set qdfPass = CurrentDb.QueryDefs("MyPass")
qdfPass.SQL = "exec sp_myProc"
qdfPass.Execute

or

qdfPass.SQL = "exec sp_myProc Parm1"

Note how in the above I include the parmater. You could also go:

strIDDelete = inputbox("what record id to delete")

qdfPass.SQL = "exec sp_myProc " & stIDDelete

The above would assume your stored procedure accepts one parameter of a id
to delete a record for example....

So, try running your pass-though query by clicking on it...if it runs ok,
the you should be able to go:

currentdb.QueryDefs("name of query").Execute

In you case, you are opening the db by going

Set db = OpenDatabase(sdb)

Is sdb an external database here? This is an additional issue of confusing
here. I not so sure you can execute "external" queries this way. I think you
can, but you might want to expand on what this open database command is
doing here?. I would test/try the currrentdb.QueryDefs().Execute in the
ACTUAL database WITHOUT using a opendatabase command here. Also, when you
say VB code, I assume you mean access VBA code here...or are you talking
about actual VB6 or some other enviroment here?

You are using standard odbc linked tables to sql server here...right?
 
D

Dirk Goldgar

No its not a select, i'm deleting from a legacy odbc linked table
(ingres) using a pass thru
the query works from access ok but how do I get it to work from code..


A little more information would help, but maybe the problem is that your
code is not recognizing the query as a SQL pass-through query. I think this
If statement:
If db.QueryDefs(qry).Type = dbQSQLPassThrough Then

will probably not work, because the querydef Type property is a bit mask
that combines several attributes, not just dbSQLPassThrough. Try this:

If (db.QueryDefs(qry).Type And dbQSQLPassThrough) <> 0 Then
db.QueryDefs(qry).Execute
Else
' ...
End If

If you have to resolve parameters, that's going to complicate things.
Please give more information.
 
B

burningthemidniteoil

Thanks for trying to help
I did a test on the current database, as suggested i get the error
cannot execute a select query? run time error 3065.

This pass thru query is stored as an access pass thru query
'zjunk_ingres_del' (SQL text is 'delete from ltax.zjunk' )
The query it works if i click on it.

I wrote this code in the same access data base as the query dosen't
work . run time error 3065.


Sub x()
Dim db As Database
Set db = CurrentDb

sSQL = db.QueryDefs("zjunk_ingres_del").SQL
If db.QueryDefs("zjunk_ingres_del").Type = dbQSQLPassThrough
Then
db.QueryDefs("zjunk_ingres_del").Execute
End If

End Sub

It thinks i'm trying to execute a select i'm using access 2003
 
B

burningthemidniteoil

Once again have to solve my own problem - the MVPs go down in flames
once again..!

Simply set the View->Properties when in query design of property
Return Records to 'No'
other wise the query will not run as it thinks it is returning rows
and so is a select - not an action query
which you can execute - hence the 3065 error cannot run a select
query..
 
D

Dirk Goldgar

Once again have to solve my own problem - the MVPs go down in flames
once again..!

Oh, the shame!
Simply set the View->Properties when in query design of property
Return Records to 'No'

I was going to ask about that next. <g>
 
W

WylieCoyote

I know your frustration. I tried everything they recommended 3 years ago and
wasted my time too. What I use is a Access table to hold all my stored SQL
command strings that I'm going to need. The first field contains the
abbreviated command name that acts as a key to retrieve the SQL command.
The command string may contain Argument fields (?ARG1? to ?ARG#?) that I use
to insert anything I want into the command string, ie. data or commands.
While you must be cognisent of what your are doing it's a simple to use and
Sample table entry: (3 fields defined and are named immediately below)

SQLCmdName SQLCmd TrgtTbl
GetInvoices "Select invoice.co_id, invoice.st_id, invoice.inv_date,
invoice.inv_id,
invoice.cust_id, invoice.inv_acct_yr_mon, invoice.inv_acct_day,
invoice.inv_trans_type, invoice.inv_cntr_per, invoice.inv_sale_per,
invoice.inv_cashier_id, invoice_part.prod_line,
invoice_part.prod_grp_id,
invoice_part.sku_id, invoice_part.invp_item_billing,
invoice_part.invp_qty_billed, invoice_part.invp_unit_price,
invoice_part.invp_unit_cost, invoice_part.invp_unit_rebate,
invoice_part.invp_rebate_item
From invoice, invoice_part, customer
where invoice.co_id = invoice_part.co_id AND invoice.cust_id =
customer.cust_id AND
customer.co_id = invoice.co_id AND
customer.cust_category <> 17 and
customer.cust_category <> 20 and
invoice.st_id = invoice_part.st_id And
invoice.inv_date = invoice_part.inv_date AND
invoice.inv_date >= ?ARG1? AND
invoice.inv_date <= ?ARG2? AND
invoice.inv_id = invoice_part.inv_id AND
invoice.inv_id > 0 AND
invoice.inv_trans_type < 5 AND" Transactions

Here's the VB code:
Option Compare Database
Option Explicit
'This is a standalone function that returns an edited SQLcmd string
Public Function EditSqlcmd(Cmd As String, ArgNum As Integer, Argmt As
Variant) As String
'Replaces templated argument strings "?ARG##?" with some date, number or
string value
EditSqlcmd = Replace(Cmd, "?ARG" & ArgNum & "?", Argmt)
End Function
Public Sub GetCmdRS(RecSet As ADODB.Recordset, cnxn As ADODB.Connection)
' This subroutine retrieves all of the SQL Command string templates used in
this subsystem
Dim SQLsdscmdtxt As String
SQLsdscmdtxt = SQLCmd ' SQLCmd is a function call
RecSet.Open SQLsdscmdtxt, cnxn
End Sub
' This function builds the standard command to retrieve the table of all SQL
commands
Public Function SQLCmd() As String
SQLCmd = "SELECT SQLCmds.SQLCmdName, SQLCmds.SQLCmd, SQLCmds.TrgtTbl
From SQLCmds WITH OWNERACCESS OPTION"
End Function

Public Function GetSQLCmd(SQLCmdRs As ADODB.Recordset, SQLCmdstr As String)
As String
' Retrieve the SQL command from the table of SQL command Templates
SQLCmdRs.Filter = adFilterNone
' SQLCmdstr contains the name of the command to retrieve
SQLCmdRs.Filter = "SQLCmdName = '" & SQLCmdstr & "'"
GetSQLCmd = SQLCmdRs.Fields("SQLCmd")
End Function


This code is called in vb or vba using the following:

SQLCmd2 = GetSQLCmd(SQLCmds, "UPTrnMthYr")
SQLCmd2 = EditSqlcmd(SQLCmd2, 1, nxtMTH)
SQLCmd2 = EditSqlcmd(SQLCmd2, 2, nxtYR)


' Update the Transaction Month and Year table to the month and year
being processed
DoCmd.RunSQL SQLCmd2

Define all the variables lile SQLCMD2 as string, etc.

I create the SQLCmd table manually because it doesn't change.

The application using the above opens and gets the complete SQLCmd table
once and keeps the recordset around for use by other apps. These apps filter
the table retrieving the template desired, modifies the command and executes
it.

Good luck,
WylieCoyote
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top