<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Published on
12,972 Points
9,872 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
Comment
Author:adraughn
5 Comments
LVL 13

Author Comment

by:adraughn
It's fine, I don't mind giving ownership rights to EE.

-a
0

Expert Comment

by:owetch
why would you have an access database front and sql back? why not sql only app.? is there a performance advantage to using both at the same time?

Currnetly we are looking at moving from access to sql, but we are finding it hard to pin point the the practical benefits of using both or one over the other.  our challenge is to take a really good vb/access app and increase performance for a large number of users accessig it concurrently thru fat client or rdp [ts].

Your thoughts?
0
 

Administrative Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
owetch:

<<why would you have an access database front and sql back? why not sql only app.? is there a performance advantage to using both at the same time?>>
 
  Please post this as a question in the MS Access Zone. Comments here should be about the article.
  If you need assistance with that, please let me know.

Thanks,
Jim Dettman
MS Access Zone Advisor
0
LVL 26

Expert Comment

by:Nick67
For my part, I would like to see you amend this article significantly, as it gives short shrift to many complex issues.

"Running stored procedures on SQL Server vs. canned queries in MS Access will increase performance of your application." "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."

That is a vast over-simplification of a very complex subject.  The Jet/ACE engine has become very sophisticated.  There is very little, if any, performance difference between an Access SELECT statement from linked tables, an Access SELECT statement from a linked, indexed view and a stored procedure when the query does not wind up invoking a full-table download to the client-end.  More accurate and more succinct would be to say "Converting queries that you find to be long-running in Access can yield significant performance enhancements."  because that is certainly true.

For those who know nothing about the subject and are exploring it, they are likely to have installed SQL Server Express Edition.  The 2008 R2 version has gone away from the venerable Northwind sample database to PrescriptionContoso.  To have built your example from the MS sample database installed in whatever YOU are using would have made your article more useful.  You then could have provided an mdb/accdb sample that linked to that database.  The reader would then need to use the Linked Table Manager to re-connect their tables locally, but you have discussed DSN requirements in small detail already.  To then have provided sample sprocs, where in testing them  vs. their Access query counterparts you would be able to demonstrate clear performance enhancements would have been outstanding.  Cases where that can be the case are things like joins on unindexed text fields, and the use of LIKE in the WHERE clause on unindexed (usually text) fields as well.  Research would have suggested other examples, as well.  Very complex UNION queries come to mind.

Your code is sound, but you didn't comment it at all.  You didn't explain to the reader that every saved query can be accessed in code through the QueryDefs collection.  Nor did you explain that you can permanently alter the SQL statement of that query using your code.  Nor did you point out to the reader that SQL Server has different requirements for enclosing parameters.  'Strings' must be enclosed with single parentheses.  Dates should also be passed in a 'strings' and MUST be in US Date format if sent in as 'xx/xx/xx'  Or that SQL Server's wildcard is '%' and not '*'
All of which would have been really good.

And then you conclude with the questionable "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."

Bypassing Jet/ACE does not always enhance performance.  Bypassing Jet/ACE when a) the nature of the query will force Jet/ACE to request all the data in all the tables involved to complete the processing b) the amount of data requested is large and/or the network is slow and c) the server has the horsepower to complete the query more expeditiously than the client can result in performance enhancements.

Your article is a good first draft, but only that.  Don't take my word for it.  As of now, 5523 people have viewed it, no one has voted it helpful.  Although that may be a site artifact, as you first submitted it years ago, and perhaps this commentary comes out of the blue.  I do not mean to offend, but rather to constructively criticize. I am looking at the article because it was linked in my EE Newsfeed, and is now likely drawing significant traffic.  That's my 2 cents, anyway.

Nick67
0
LVL 1

Expert Comment

by:stevengrogan
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

Open in new window


There is a serious error in your code here. The call to open the report should be:
DoCmd.OpenReport strReport, acPreview

Open in new window


Your code calls a nonexisting variable

Also, you may want to order some of the steps differently, as you are referencing adding function code to a form that isn't referenced on the creation until farther down in the instructions.
0

Featured Post

The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

Join & Write a Comment

With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month