How To Pass User Parameters to a SQL Stored Procedure using a Pass-Thru query

Published on
14,071 Points
1 Endorsement
Last Modified:
Article Description

Many new developers struggle with utilizing SQL when working with a SQL backend and an Access frontend. Running stored procedures on SQL Server vs. canned queries in MS Access will increase performance of your application.

In this article I will review step-by-step how to pass parameters from a user form in MS Access to a stored procedure in SQL Server, returning the results.

Part One: Creating the Pass-Thru Query
Part Two: Dynamically Changing the SQL
Part Three: Create Test Report
Part Four: Passing Values from User Form

Setup for the Example

1. You will need to create a table tblTest that has the following fields:

EmployeeName  (Example: Adria Bynum)
HoursLogged (Example: 60)
ReportMonth (Example: Dec-08)

2. You will need a stored procedure on the server that includes a parameter. This will allow you to pass the parameter from MS Access to SQL. Here is an example of a stored procedure that you may create:

CREATE PROCEDURE [dbo].[upMySprocName]
@MyParam1 Varchar(50)
FROM tblTest t
WHERE ((t.ReportMonth)= @MyParam1)

In this example, the stored procedure will be pulling everything from the table for a specific reporting month.

Part One: Creating the Pass-Thru Query

About Pass-Thru Queries:

Pass-Thru queries bypass JET and are a direct connection to the server. The results of these queries are read only but they are very useful as data sources for reports and non-updateable form views and offer improved performance when working with a SQL backend.


1. Create a new query in design view in Access

2. From the query menu, choose 'SQL Specific', 'Pass Thru Query'

3. Type execute upMySprocName (Note that the prefix 'sp' is used for system stored procedures. 'up' should be used for user stored procedures)

4. From the query View menu, select Properties, and put this line (modify it) in the ODBC Connect Str property:
ODBC;DRIVER={sql server};DATABASE=YourDatabaseName;SERVER=YourServer_IP_Address;Trusted_Connection=Yes;

5. Save the query as 'qryPT' and close

Part Two: Dynamically Changing the SQL

About the Function:

This function will allow you to dynamically change the SQL of the Pass Thru query, allowing you to pass parameters from the user form.


Add this function in the VBA Editor of your form

Function ChangeSQL(strQry As String, strSQL As String) As String

    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Set db = CurrentDb
    Set qd = db.QueryDefs(strQry)
    qd.SQL = strSQL
    ChangeSQL = qd.SQL
    Set qd = Nothing
    Set db = Nothing
End Function

Part Three: Create Test Report

About the Report:

The user will open this report via the user form. They will first select the report name, then select the month in 'mmm-yy' format, then click on the command button cmdButton.


1.      Create a new report named 'rptTest'.
2.      Make the data source for the report 'qryPT'.
3.      Drag all available fields onto the report.
4.      Close and save.

Part Four: Passing Values from the User Form

About the Form:

In this example, your user form will contain the following:

1.      A combobox with the report name. The data source for the report should be the Pass Thru query, 'qryPT'.

2.      A combobox named 'cmbReportMonth' with the report month and year in mmm-yy format.

3.      A command button named 'cmdButton', that has a caption titled 'Open Report'.

4.      The form should be named 'frmTest'


Put this code on the OnClick event of cmdButton:

Private Sub cmdButton_Click()
Dim strQry as string
Dim strSQL as string
Dim strOldSQL as string
Dim strReport as string

On Error GoTo tagErr

            strQry = "qryPT"
            strSQL = "execute upMySprocName"
            strOldSQL = ChangeSQL(strQry, strSQL & "'" & _
                    Forms![frmTest]![cmbReportMonth] & "'") 'etc. for more parameters
            strReport = "rptTest"
            DoCmd.OpenReport strRpt, acPreview
            Exit Sub

            msgbox err.description

End Sub


In this example, your user is able to select a report and report month for the data that they want returned. The report month is successfully passed to the SQL stored procedure thru the Pass Thru query, bypassing JET and improving performance.

For more information on Pass Thru Queries, I recommend the following:




For more information on building a strong application using MS Access as a frontend and SQL as a backend, here is a link to one of my prior threads with my recommendations:


Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free