http://www.databasejournal.com/features/msaccess/article.php/3407531/How-to-Execute-SQL-Stored-Procedures-from-Microsoft-Access.htm
Featured Database Articles
Posted Sep 17, 2004
How to Execute SQL Stored Procedures from Microsoft Access
By Danny Lesandrini
The simplicity and popularity of SQL Server means that
more and more developers who build applications with Microsoft Access will want
to learn how to take advantage of server side processing using SQL Server Stored
Procedures. The following article will demonstrate a simple method for
executing procs from Access. In addition, we'll cover questions like
these:
-
How to login to SQL Server from Access and refresh object connect
properties.
-
Check for existence of stored procs and add new ones where
necessary.
-
Build dynamic SQL Pass Through query with parameter values.
-
Display the results of the query output in a listbox.
The
download for this article contains all the
code from the article in both Access 97 and Access 2003 versions. You
will need access to the Pubs database on a Microsoft SQL Server, including
the necessary permissions to read the sysobjects table and create stored procs.
The best way to understand the process is to step through the code in debug
mode and watch as it happens, but in the mean time let's look at some of the
more key code scripts.
Login to SQL Server
The first step, if your application has not already managed it, is to
collect and apply the login credentials to your local DAO table and/or
query objects. I wrote an article about this process about four years ago and
it is still available here at Database Journal. To read it for yourself,
follow this link:
ODBC
DSN-Less Connection Tutorial
In fact, that's where I started when preparing the code for this article, so if
you've already read that article, this login screen will be familiar to
you. The premise is simple: collect login parameters, test validity
and relink local tables and Queries.
If you name your text boxes well, the code will be very easy to read. You
will need to test each one for existence, except for the password, which may be
blank, although you should be scolded if you allow blank passwords.
' Check for existence of Server, Database and User Name.
' If missing, inform user and exit.
If IsNull(Me!txtServer) Then
strMsg = "Enter name of your company's Server." & _
& "(See your database administrator)"
MsgBox strMsg, vbInformation, "Missing Data"
Me!txtServer.SetFocus
ElseIf IsNull(Me!txtDatabase) Then
strMsg = "Enter name of database. (Example: PUBS)"
MsgBox strMsg, vbInformation, "Missing Data"
Me!txtDatabase.SetFocus
ElseIf IsNull(Me!txtUID) Then
strMsg = "Enter user login. (Example: sa)" = ""
MsgBox strMsg, vbInformation, "Missing Data"
Me!txtDatabase.SetFocus
Else
strServer = Me!txtServer
strDatabase = Me!txtDatabase
strUID = Me!txtUID
' Password may be NULL, so provide for that possibility
strPWD = Nz(Me!txtPWD, "")
' Prepare connection string
strConnect = "ODBC;DRIVER={SQL Server}" _
& ";SERVER=" & strServer _
& ";DATABASE=" & strDatabase _
& ";UID=" & strUID _
& ";PWD=" & strPWD & ";"
End If
As you can see from the screen shot, the code also allows you to read
connection information from an ini file. While I will not take the time
to list the code for that here, it is included in the download. One might
suggest that a DSN be used in place of dynamic parameters, but I have found
that it is more clumsy to set up user desktops with a new DSN than it is to
simply allow them to enter login credentials from the application and save them
for subsequent logins. That is my preference, so I will not cover DSNs in
this article.
Make Sure Your Procs Exist
The sample application (shown below) leverages
four existing stored procedures from the Pubs database, and two new ones that I
created. The metadata for these procs is stored in a local Access
database, including the script to create each of them. The next step in
our process is to test for their existence and create them if they are missing.
Private Sub CheckForStoredProcs()
On Error Resume Next
Dim qdf As dao.QueryDef
Dim dbs As dao.Database
Dim rst As dao.Recordset
Dim sSQL As String
' Open a recordset of the stored procs to be used, and tested for.
Set dbs = CurrentDb
sSQL = "select ProcObjectName, ProcText from tblSQLProcList"
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
' Create a querydef object and set its CONNECT property.
Set qdf = dbs.QueryDefs("qryCheckProcs")
qdf.Connect = strConnect
qdf.ReturnsRecords = True
' Loop through the list, check for existance and create if needed.
Do Until rst.EOF
sSQL = "select count(*) as ProcExists from sysobjects " & _
"where Name='" & rst!ProcObjectName & "'"
qdf.SQL = sSQL
If DLookup("[ProcExists]", "[qryCheckProcs]") = 0 Then
' (ProcText contains the script to create the stored proc.)
qdf.SQL = rst!ProcText
qdf.ReturnsRecords = False
qdf.Execute
End If
rst.MoveNext
Loop
Err.Clear
Set rst = Nothing
Set qdf = Nothing
Set dbs = Nothing
End Sub
The key part in the above
script is the
QueryDef.ReturnRecords
property, which must be set to TRUE when selecting records and FALSE when
executing a script to create and/or alter database objects. By the way,
these procs are created automatically when you successfully login to SQL
Server so they will run without error when selected in the Demo application.
Prepare and Execute Parameterized Stored Procs
The above screen shot illustrates the context
sensitive nature of the criteria collection process. Not all procs take
the same parameters. The one selected, df_Orders, requires a date range
while df_Employee takes an employee name. The table that stores the list
of procs and their scripts also exposes attributes that identify which criteria
options to enable. As you click through the list, criteria collection
boxes enable and disable themselves accordingly.
The code for building the SQL necessary to execute the procedures with the
correct parameter values is not especially reusable, but the principal is
simple and extensible. It looks like this and the output of this script
is displayed just above the listbox containing the query results. (See screen
shot above.)
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryCurrentProc")
sSQL = "exec " & lstProcs.Column(1)
qdf.SQL = sSQL
If txtPercent.Enabled Then sSQL = sSQL & " @percentage=" & txtPercent
If txtLoLimit.Enabled Then sSQL = sSQL & " @LoLimit=" & txtLoLimit & ", "
If txtHiLimit.Enabled Then sSQL = sSQL & " @HiLimit=" & txtHiLimit & ", "
If txtType.Enabled Then sSQL = sSQL & " @Type='" & txtType & "'"
If txtEmployee.Enabled Then sSQL = sSQL & " @Employee='" & txtEmployee & "'"
If txtStart.Enabled Then sSQL = sSQL & " @Start='" & txtStart & "', @End='" & txtEnd & "'"
Me.lblQuery.Caption = sSQL
qdf.SQL = sSQL
The effect of this code is to swap out the SQL text property
of the SQL Pass Through query that will be used to retrieve data. The
final step is to load the query into the listbox. Just to make sure, the
code assigns our connect string to the query's connect property. Next,
the listbox RowSource property is set to our newly updated query object and is requeried.
qdf.Connect = strConnect
Me.lstResults.RowSource = "qryCurrentProc"
Me.lstResults.Requery
In addition to exposing the SQL text property of the query
on the Demo application main form, there is a button that will open the actual
query, along with its properties window, where you can examine all the
properties exposed for a SQL Pass Through query. Below you can see
that for our ListBox query, the Return Records property is TRUE and our SQL
Server login credentials are displayed in the ODBC Connect String property.
There are a number of subtleties that are not described above, like trapping
for missing login parameters and moving back and forth between the login screen
and the main form. The
download
has the complete code listings and while it is nearly complete, you will likely
discover some quirkiness to it. Please overlook the minor errors and take
it for what it is, a tutorial.
Also, as a final caveat I would like to admit that there other ways to
accomplish the same feat, including the use of Access Data Projects (ADPs).
I have not expanded on these alternates here because I prefer the above
method. In fact, I am currently considering converting a client's ADP
back to a traditional MDB and implementing the above process for data access to
SQL Stored Procs. I suppose that will give me a benchmark to test the
performance difference between the access methods. If I find that the ADP
performs better, I will repent in dust and ashes and print a retraction.
CurrentDB.QueryDefs("qryCurrentProc").SQL = "df_orders '6/15/1992', '9/15/1994'"
Sweet, huh? By assuming that our Access query has had its
CONNECT property set, we just reduced the last code script to a single
line. This is easy to call for combobox sources, listboxes and even
Access reports. That is really all you need to get your Access
applications to play nice with SQL Server stored procedures. Happy
computing!
»
See All Articles by Columnist Danny J. Lesandrini
