0

I'm trying to create in MS Access a pass trough query which will be connected to SQL server and use combo box from the form as a filter parameter in the WHERE statement part.

I know that connection and everything works because if I enter 

SELECT * FROM mrch.Promo_Request_Base

I receive all the results.

Now when I try to enter something like

SELECT * FROM mrch.Promo_Request_Base WHERE mrch.Promo_Request_Base.Requestor_Name = 'UserABC';

then it also works.

It does not work for me if I enter SQL like this: 

SELECT * 
FROM mrch.Promo_Request_Base
WHERE (((mrch.Promo_Request_Base.Requestor_Name) = [Forms]![f_PromoRequest_VIEW_Header_001a]![Combo133]));

I also tried this:

SELECT *
FROM mrch.Promo_Request_Base
WHERE (((mrch.Promo_Request_Base.Requestor_Name) = [Forms]![f_PromoRequest_VIEW_Header_001a]![Combo133].Columns(0)));

[Combo133] has value 'UserABC' in it.

I would be very thankful if you could help me.

Suraj Kumar
  • 5,547
  • 8
  • 20
  • 42
Erik Toman
  • 1
  • 1

3 Answers3

0

You don't.

Pass-through queries are passed to the data source as-is, and aren't parsed by Access at all. This means there's no way to use form-based parameters in a pass-through query.

Instead, use VBA to set parameters.

For how to do that, see How do I use parameters in VBA in the different contexts in Microsoft Access?. Specifically, the section on DAO applies to pass-through queries.

This also means you can't open the query for displaying. Use a form in datasheet view using VBA to set its own recordset instead. Do note that if a recordset requires parameters to be requeried, this can lead to trouble on sort/filter.

Erik A
  • 31,639
  • 12
  • 42
  • 67
0

A pass-through query means it is executed server side. That server can no more go look into access and pull data then go and try and steal all your emails or financial data on your computer.

the simple solution then is to "process" or "evaluate" the expression BEFORE you send it to sql server. You can use the following:

Dim strSQL     As String

strSQL = "SELECT * From mrch.Promo_Request_Base " & _
         "WHERE mrch.Promo_Request_Base.Requestor_Name = '" & _
         [Forms]![f_PromoRequest_VIEW_Header_001a]![Combo133] & "'"

With CurrentDb.QueryDefs("qryPass")
   .SQL = strSQL
End With

' now code here to open form, or say launch report

DoCmd.OpenReport "rptPromoList", acViewPreview

Note that you have to ensure that the sql is formatted correctly for the server side.

So, in above, I am assume Requestor_Name is a text type of field. So,, that means you have to place quotes (I used single) around the expression. If that column you get from the combo box is a number, then the quotes are not required, and you would use this:

strSQL = "SELECT * From mrch.Promo_Request_Base " & _
         "WHERE mrch.Promo_Request_Base.Requestor_Name = " & _
         [Forms]![f_PromoRequest_VIEW_Header_001a]![Combo133]

So the other code would be the same - the only change in above was how I left out the adding of quotes (') around the expression.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
0

Many thanks for your help.

At the end I did it differently. Im changing the PassTrough query via VBA :

Private Sub Command159_Click()

Dim db As dao.Database
Set db = CurrentDb
Dim qdf As dao.QueryDef

Set qdf = db.QueryDefs("q_PassThrough_VIEW_001a")


qdf.SQL = "SELECT * From mrch.Promo_Request_Last_Version_rpt_v " & _
"WHERE mrch.Promo_Request_Last_Version_rpt_v.F_Qtr_CD LIKE '" & _
[Forms]![f_PromoRequest_VIEW_Header_001a]![Combo145] & "%'" 

qdf.Close
db.Close
Set qdf = Nothing
Set db = Nothing

End Sub
Erik Toman
  • 1
  • 1