Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old October 20th, 2003, 12:41 PM
Registered User
Join Date: Oct 2003
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default pass-through query with input parmeter

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!


Old October 20th, 2003, 02:25 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

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

 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); _

    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
    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)

 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

    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

    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.



Old April 2nd, 2004, 06:35 PM
Registered User
Join Date: Mar 2004
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts


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?
Old April 22nd, 2004, 07:42 PM
Registered User
Join Date: Mar 2004
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts


In reponse to:

"Hello blinton25

You received the following message from: benmay14 ([email protected])

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:


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


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()

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

EXEC TArrivalsByRegion

4. Create a Stored Procedure with the following:


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


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


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.
Old February 8th, 2018, 10:21 AM
Registered User
Join Date: Feb 2018
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default pass throuh query with parameter in report

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:
   Private Sub Report_Open
   'it is enough to write:
   Dim qdf As QueryDef
         Set qdf = currentdb.QueryDefs ("TArrivalsByRegion")
   qdf.SQL = "EXEC TArrivalsByRegion" & Forms! DateRangePreviousCurrentYear! PrevYear & "," & Forms! DateRangePreviousCurrentYear! CurrYear
the rest of the article is ok for me
my english is not operational, I hope that the translator gives a good translation

Similar Threads
Thread Thread Starter Forum Replies Last Post
pass a form input as a parameter to an access quer howbadboy Javascript How-To 1 March 1st, 2005 05:49 AM
SQL query from input form skywalk22 SQL Server ASP 7 October 21st, 2004 09:24 AM
Pass-Through Query Optimization redrobot5050 Access VBA 3 June 18th, 2004 01:25 AM
Parmeter query probs / confusion socoolbrewster Access 3 March 4th, 2004 12:10 PM
passing input to a query mirage Access 3 October 23rd, 2003 08:43 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.