Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS Access

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



MS Access Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date