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!
