pass through query variables

[SimonT]

Hi Guys,

I am looking for some help with a pass through query. I have a number of
reports that allow users to select values from combo box's and want to know
how I can pass this data in a pass through query.

I have looked through and searched but seem to just get sites that I have to
register to get some details, but do not wish to register elsewhere to get
some info.

If any has an example or links to any sites that would cover this I would
appreciate it.

An example of what I would want to pass is

[forms]![frmReports].[txtsupplierID]
[forms]![frmReports].[txtproductID] (the syntax might be wrong, just for
example)

Thanks
Si

 

[golfinray]

What are you trying to pass the data to? The same database or another
database? Another table? Another query?

 

[Phil Smith]

What are you trying to pass the data to? The same database or another
database? Another table? Another query?

I am going through this now. You are going to have to build the
passthrough query on the fly using VB., save it as a stored procedure,
then run it. You use the control's data when you build the SQL string.
When your code is finished, you will not have any references to any of
your controls, just the data which is coded as a constant into the string.

You will soon run into my question, which I now post.

Phil

 

[SimonT]

Cheers Phil,

Many thanks for that, can I ask how you got on?...I have just seen the
response to your question and just trying to get my head around it to see if
I can get this to work.

Regards
Si

 

[vanderghast]

You can try to make a "call back":


SELECT *
FROM somewhere
WHERE id IN

(SELECT CustomerID
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb';
'admin';'',Customers)
)





While this query is a pass-trough query, executed on the MS SQL Server, it
'calls' the Northwind.mdb database to get some information, here the
table/query Customers, with admin logon, using OPENROWSET.

In your case, that would probably call your own mdb file, so it would really
a "call ME back", and instead of the table Customers, it could be a table
where you would have put the parameters. Since that code really call Jet,
not Access, I suspect that


SELECT FORMS!formName!ControlName


would not work, since a brand new Jet instance won't know anything about
your existing Access instance, and that is why you probably need to use a
table to refer to the parameters. You can also use a linked_server, instead
of OPENROWSET.



Note that if you use something else than an mdb, then you have find the
appropriate provider, which is probably NOT : 'Microsoft.Jet.OLEDB.4.0'



Or, as someone has already suggested, you can produce ad hoc code, as a
string to be executed by your pass-trough query.




Vanderghast, Access MVP

 

[Phil Smith]

So Far So good. You need to use VB to build your sql string. Your SQL
string is the final SQL you will be passing through.

As you build the string, your variables, (form controls, etc.) are put
into the string as values.
if
[forms]![frmReports].[txtsupplierID] = "txt117"
you would have in code
SQLWHERE = "WHERE invoice.Supplier =" & chr(34) &
[forms]![frmReports].[txtsupplierID] & chr(34)

This produces

WHERE invoice.Supplier ="txt117"

You put all of your pieces together into a SQL statement that is ready
to go, you save it out into a premade placeholder query, which you can
then run.

I can give you one of my examples if it will help.

 

[SimonT]

Hi Pete,

If you could send me your example I would really appreciate that thanks

Regards
Si

So Far So good. You need to use VB to build your sql string. Your SQL
string is the final SQL you will be passing through.

As you build the string, your variables, (form controls, etc.) are put
into the string as values.
if
[forms]![frmReports].[txtsupplierID] = "txt117"
you would have in code
SQLWHERE = "WHERE invoice.Supplier =" & chr(34) &
[forms]![frmReports].[txtsupplierID] & chr(34)

This produces

WHERE invoice.Supplier ="txt117"

You put all of your pieces together into a SQL statement that is ready to
go, you save it out into a premade placeholder query, which you can then
run.

I can give you one of my examples if it will help.

Cheers Phil,

Many thanks for that, can I ask how you got on?...I have just seen the
response to your question and just trying to get my head around it to
see if I can get this to work.

Regards
Si

 

[Phil Smith]

Function FGDetailsSQLCreate()

Dim Q As QueryDef, DB As Database
Dim SQLSTRING As String
SQLSTRING = "SELECT accounting.post_table, accounting.post_date,
accounting.post_process, accounting.post_id FROM accounting WHERE
accounting.post_table="
SQLSTRING = SQLSTRING & Chr(34) & "fg_order_h" & Chr(34) & " AND
accounting.post_date>="
SQLSTRING = SQLSTRING & Chr(34) & Format(DateAdd("d", -180, Now()),
"yyyy-mm-dd hh:mm:ss") & Chr(34)
SQLSTRING = SQLSTRING & " AND accounting.post_process=" & Chr(34) &
"WIP Receive FG" & Chr(34) & ";"
'Send SQL to stored query
Set DB = CurrentDb()
Set Q = DB.QueryDefs("FGData180Days")
Q.SQL = SQLSTRING
Q.Close

End Function
==========================================
This produces

SELECT accounting.post_table, accounting.post_date,
accounting.post_process, accounting.post_id FROM accounting WHERE
accounting.post_table="fg_order_h" AND accounting.post_date>="2009-10-25
09:51:37" AND accounting.post_process="WIP Receive FG";
===================================================

Look to the part Chr(34) & Format(DateAdd("d", -180, Now()), "yyyy-mm-dd
hh:mm:ss") & Chr(34)

The chr(34) puts quotes in the string itself, neccessary if you are
working with text.

the format() command could be any reference to a form control, formula,
whatever you wanted. FGData180Days a query I created as a passthrough
query, with the proper connection string. All my code does is build the
SQL string and insert/replace whatever is already in FGData180Days.

You can run that query just like you would any other query, from code,
from a macro, with another query, just like normal.

Phil

 

[SimonT]

Hi Phil,

Many thanks for that post, I am just trying to complete this for myself now
and just a little stuck, always the same when trying to compare what you
have done to what I am doing, can see it, just trying to structure it
properly, so this is what I have got:

I have create a Pass through Query PT_csr01 which has the connection string
in and works fine.

SELECT Item, productID, cost, CustID, price, pricesID, margin, margin /
CASE WHEN cost = 0 THEN 1 ELSE cost END AS pcent, available
FROM dbo.View_CustomPricesandMargin01
WHERE (CustID = 44)

I have then constructed a module which I have called modPTQ which I will add
my other pass through functions in to call from (is that the correct way or
should I create a module for each function?)

My function is:

Function CSR01SQLCreate()

Dim Q As QueryDef
Dim DB As Database
Dim SQLCSR01 As String

SQLCSR01 = "SELECT dbo.View_CustomPricesandMargin01.Item, "
SQLCSR01 = SQLCSR01 & "dbo.View_CustomPricesandMargin01.productID,"
SQLCSR01 = SQLCSR01 & "dbo.View_CustomPricesandMargin01.cost,"
SQLCSR01 = SQLCSR01 & "dbo.View_CustomPricesandMargin01.CustID,"
SQLCSR01 = SQLCSR01 & "dbo.View_CustomPricesandMargin01.price,"
SQLCSR01 = SQLCSR01 & "dbo.View_CustomPricesandMargin01.pricesID,"
SQLCSR01 = SQLCSR01 & "dbo.View_CustomPricesandMargin01.margin,"
SQLCSR01 = SQLCSR01 & "dbo.View_CustomPricesandMargin01.margin / CASE WHEN
cost = 0 THEN 1 ELSE cost END AS pcent," 'To avoid any divide by null values
SQLCSR01 = SQLCSR01 & "dbo.View_CustomPricesandMargin01.available "
WHERE dbo.View_CustomPricesandMargin01.CustID = ""
SQLCSR01 = SQLCSR01 & "qcboCust"


Set DB = CurrentDb()
Set Q = DB.QueryDefs("PT_csr01")
Q.SQL = SQLCSR01
Q.Close

End Function

My form uses the combo box to select a customer and use the CustID to filter
the results for the specific customer. Also as I am using a combo box to
pass the CustID so I would not need the Chr(34)?

My question is how do I then call the function, do I use DoCmd.CallFunction
"CSR01SQLCreate()" which I have tried but it fails, or something else.

Hopefully there is enough info in the post above, but if you need any
further information please ask.

Once I have this in my mind< I can go ahead and complete my other pass
through queries and log this as knowledge.

Many thanks for your help

Si.