Hi friends,

How to pass a parmater with path-through
query in Access2000?
i created a stored procedure which need to input a parameter. I want to run this procedure by path-through query.

Thanks for your help!

Jimmy

Hi Jimmy,

Here's an approach I picked up from the "Microsoft Access Developer's Guide to SQL Server" by Mary Chipman and Andy Baron.

This pass-through query executes a parameterized stored procedure against the SQL Server version of the Northwind database using a DSN-less ODBC connection.

The query returns a single employee record using EmployeeID as criteria.

First the pass-through query get's built, then a stored procedure gets created on SQL Server which the pass-through query will execute, then some code gets written to re-build the pass-through query everytime a new employee record is requested.

The ExecutePassThrough sub recieves an Employee ID number as an argument and concatenates it to the Pass-Through query's SQL. BuildPassThrough is then called which uses ADOX to reset the pass- through query's command text.

 ================================================== =======
 Design the Pass-Through Query in Access (.mdb)
 ================================================== =======

 1. In Query design view, from the main menu select
    Query | SQL Specific | Pass-Through.

 2. Type "EXEC up_parmsel_Employees" as the query's SQL
    statement.

 3. Click the properties button on the Toolbar to open the
    Query properties dialog. Enter the following DSN-less
    connection string in the ODBC Connect Str property:

    ODBC;Driver={SQL Server};Server=(local); _
    Database=Northwind;Trusted_Connection=Yes

    NT authentication is used here.

    All the other default properties are fine. Clicking
    the ellipsis in the ODBC Connect Str property
    field will open the DSN Data Source dialog if you
    want to use a DSN.

 4. Save the query as "qsptEmployees" and close without
    running (the stored procedure doesn't exit yet).


 ================================================== =======
 Create the Stored Procedure in SQL Server 2000
 ================================================== =======

 1. With SQL Server running open the Query Analyzer.

 2. Select the Northwind database form the Toolbar's
    drop-down list.

 3. Type the following SQL in the Query Analyzer, parse,
    and run:

    CREATE PROC up_parmsel_Employees
      @EmployeeID int = NULL
    AS
    SELECT EmployeeID, LastName, FirstName, Title
    FROM Northwind.dbo.Employees
    WHERE EmployeeID = @EmployeeID

 4. Test that the proc was created successfully by
    clearing the Query Analyzer window and running the
    following command in the Query Analyzer:

    Exec up_parmsel_Employees 1

    A single employee record for "Nancy Davolio" should be
    displayed. The query and the proc it executes are now
    ready to go. Just need code to set the parameter that
    the proc expects.


 ================================================== =======
 Write the Parameter Setting Module in Access
 ================================================== =======

 1. Open a new module in Access and set a Reference to
    ADOX (Microsoft ADO Ext. 2.x for DDL and Security)
    library.

 2. Paste in the following two subs:

     Public Sub ExecutePassThrough(lngParam As Long)
       Dim strTSQL As String
       Dim strQueryName As String

       strQueryName = "qsptEmployees"
       strTSQL = "EXEC up_parmsel_Employees " & lngParam

       Call BuildPassThrough(strQueryName, strTSQL)

       DoCmd.OpenQuery strQueryName
     End Sub


     Public Sub BuildPassThrough( _
       ByVal strQName As String, _
       ByVal strSQL As String)

       Dim cat As ADOX.Catalog
       Dim cmd As ADODB.Command

       Set cat = New ADOX.Catalog
       Set cat.ActiveConnection = CurrentProject.Connection

       Set cmd = cat.Procedures(strQName).Command

       ' Verify query is a pass-through query
       cmd.Properties( _
          "Jet OLEDB:ODBC Pass-Through Statement") = True

       cmd.CommandText = strSQL

       Set cat.Procedures(strQName).Command = cmd

       Set cmd = Nothing
       Set cat = Nothing

     End Sub


 ================================================== =======
 Test the Procedures, Resetting the Queries Parameters
 ================================================== =======

 1. In the immediate window, type the following and hit
    Enter:

    ExecutePassThrough (1)


 2. A query should open in datasheet view displaying Nancy
    Davolio's employee record.

    Or you could return the result set to an ADO recordset
    with:

    Set rst = New ADODB.Recordset
    rst.Open strQueryName, CurrentProject.AccessConnection

    You could use the following syntax:

    lngParam = [Forms]![frmSearch]![txtSearchValue]
    Call ExecutePassThrough(lngParam)

    to get the parameter value from a form.

HTH,

Bob

Hello,

Tried this method for passing parameters and it worked great. However, I need to return the resultset to a report, and not sure how to call the module from the report in order to obtain the resultset, any idea?

Hello,

In reponse to:

"Hello blinton25

You received the following message from: benmay14 (blm14@columbia.edu)

At: http://p2p.wrox.com/

Did you ever get report using a pass through query with arguments
problem solved? I am trying to do this myself... The problem I am describing
is available here:

http://p2p.wrox.com/topic.asp?TOPIC_ID=5485

Any help you could provide me would be greatly appreciated. Thanks!
"

Hi,

Sorry about the delay in responding, here goes:

1. Make the record source of the report TArrivalsByRegion.

2. In report open, create a querydef, to which you will append your form parameters.

Private Sub Report_Open(Cancel As Integer)

    Dim intX As Integer
    Dim qdf As QueryDef
    Dim frm As Form


          ' Set database variable to current database.
     Set dbsReport = CurrentDb
     Set frm = Forms!DateRangePreviousCurrentYear
     ' Open QueryDef object.
     Set qdf = dbsReport.QueryDefs("TArrivalsByRegion")


 Dim rstReport As DAO.Recordset

qdf.SQL = "EXEC TArrivalsByRegion " & Forms!DateRangePreviousCurrentYear!PrevYear & "," & Forms!DateRangePreviousCurrentYear!CurrYear
qdf.ReturnsRecords = True


 ' Open Recordset object.

      Set rstReport = qdf.OpenRecordset()
rstReport.MoveFirst


3. Create a Passthough query which contains the following statement:

EXEC TArrivalsByRegion

4. Create a Stored Procedure with the following:


CREATE PROCEDURE TArrivalsByRegion

@PrevYear1 varchar(4),@CurrYear1 varchar(4)

AS

SELECT regionorder, Regions3, 1 As Quarter, January as A, February as B, March as C FROM dbo.RegionofResidenceCrosstabFunc(@PrevYear1,@Curr Year1)

Go


Interesting thing to note, after I run the code and check the Passthrough query it has:

EXEC TArrivalsByRegion 2001,2003

if I used the parameters 2001, 2003.

Looks like if I hardcoded the values but this isn't so because if I try different parameters, e.g 2001, 2002 the Passthough query is updated with these parameters.

Let me know if this helps.

Hello,
14 years later ...
I just looked for information on passing parameters to pass throuh query. after much research I found this excellent article.
I bring my contribution on the part concerning the passing parameters to a report. In fact the trick is to dynamically change the query before launching the report. the code presented contains many useless things, I allow myself to present the following code for the part:
the rest of the article is ok for me
my english is not operational, I hope that the translator gives a good translation