Parameter in SQL Pass Thru Query

[joe789]

Hi Folks,

I am wondering if it is possible to use a parameter(s) in a SQL Pass Thru Query. For example, prompting the user to enter a data range just like a normal access query. Or pulling criteria for a field from a form? I have tried to use parameter queries in SQL Pass Thru Query but haven't had success. Am I missing something or isn't this possible. I appreciate any help!

Thank you very much,

Joe

 

[FoFa]

A PASS-THROUGH query does just that, passes the SQL right to the source. So if you are trying to use a Access Parameter, it won't fly as such in Access. You can however link the external table than write an Access query with a parm. against that table. Or you can create SQL on the fly in VBA and use that instead and take a parm. from a form or something.

 

[Pat Hartman]

I normally link my ODBC tables and so don't need to use pass-through queries. However, you can build the SQL string with VBA if you need paramters.

 

[pdx_man]

This is basically how I do it:

Create a form where I input my parameters.

Then have a command button that runs this code:

Public Sub Mytest()
Dim qdf As QueryDef
Dim Posit As Byte
Dim dbs As Database

    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("Qry_MyQuery")
    Posit = InStr(qdf.SQL, "'") - 2
    qdf.SQL = Left(qdf.SQL, Posit) & " '" & Forms![Frm_Crit]![MyParam] & "'"

 End Sub

I actually have another method where I have a form where a user 'logs' in, and then updates all pass-through queries with the user name. A switchboard form following this login where the user sets all of the parameters. These parameters are stored in a linked table that the Stored Procedures retrieve based on the user that is passed to them:

Private Sub Command4_Click()
Dim qdfLoop As QueryDef
Dim Posit As Byte
Dim dbs As Database

    Set dbs = CurrentDb
    
'... Pass-Through Queries
    For Each qdfLoop In dbs.QueryDefs
        If qdfLoop.Connect <> "" Then
            If Right(qdfLoop.SQL, 1) = "'" Then   'Check to see if pass-through
                Posit = InStr(qdfLoop.SQL, "'") - 2
                qdfLoop.SQL = Left(qdfLoop.SQL, Posit) & " '" & Forms![Frm_Pick]![MyField] & "'"
            End If
        End If
    Next qdfLoop

End Sub

***EDIT***
Here is a sample, then, of what the start of my SP looks like:

CREATE PROCEDURE dbo.sp_ir_My_SP(@usern TEXT) AS
DECLARE @end_date SMALLDATETIME
DECLARE @beg_date SMALLDATETIME

SELECT @end_date = pd.EndDate,
@beg_date = pd.BegDate
FROM dbo.ir_Param pd
WHERE pd.usern = @usern


In either method, have the pass-through query created (even with a dummy parameter) and fill in the ODBC Connect Str property.