<

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

Published on
13,598 Points
10,498 Views
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)
)
AS
SELECT *
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.

Instructions:

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.

Instructions:

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.

Instructions:

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'


Instructions:

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

tagErr:
            msgbox err.description

End Sub


Summary

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:

http://support.microsoft.com/kb/303968

http://www.techonthenet.com/access/tutorials/passthrough/basics01.php

http://www.databasejournal.com/features/msaccess/article.php/3347631/MS-Access-for-the-Business-Environment-Extend-Access-with-Pass-Through-Queries.htm

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:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_22517413.html


 
1
Author:adraughn
Enjoy this complimentary article view.

Get unlimited access to our entire library of technical procedures, guides, and tutorials written by certified industry professionals.

Get 7 days free
Click here to view the full article

Using this article for work? Experts Exchange can benefit your whole team.

Learn More
COLLABORATE WITH CERTIFIED PROFESSIONALS
Experts Exchange is a tech solutions provider where users receive personalized tech help from vetted certified professionals. These industry professionals also write and publish relevant articles on our site.
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.
Learn from the best.