This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

  • Article

Using Stored Procedures in ADPs

Burton Roberts

To get the most out of Access Data Projects, you must use stored procedures. But trying to use them for some of the most common tasks isn’t covered in the documentation. Burton Roberts shows you how to use some of ADO’s features to link stored procedures to combo boxes and forms.

Often, you want a list box or combo box to display a different list of data, depending on what the user has entered in some other part of the form. When the user selects "Priority Shipping" as the ship method, for instance, the destination list box should show only those cities to which you can send priority shipping. Typically, when creating a combo or list box like this in an Access database (MDB) file, you’ll have the list box use a query whose Where clause picks up the value of some control on the form. The process is simple: At design time, you’ll set the list box’s Row Source property to "Table/Query" and then click on the Builder button to start designing your query. In the query design grid you would enter a hard reference to the form and control that supply the parameter controlling the data to be displayed in the list box. You end up with a Select query like this as the row source for your combo or list box:

SELECT Field1 FROM Table1 
WHERE Field2 = [Forms]![frmFName]![ctlParam]

	This doesn’t work in Access Data Project (ADP) files, because the Microsoft Data Engine can’t evaluate the Access Data Project form control reference.

Using list boxes

Here’s a technique that works in Access Data Projects. In design mode, begin by setting the control’s Row Source Type to "Value List," but leave the Row Source property blank. Then, in the MSDE, create a stored procedure like this (see the sidebar "Creating Stored Procedures"):

Create Procedure procCreateMyList 
 MyParam1 varChar(50)
 As SELECT field1 FROM tblTable1
 WHERE field2 = @MyParam1

	The next step, in a standard module, is to create a public function to call the stored procedure. I called this routine myGetList. The myGetList function accepts two parameters (the name of the stored procedure and a parameter to pass to the procedure). The routine then uses ADO to run the stored procedure to return the values in the database. With the values in hand, the routine transforms them into a format acceptable to a list box.

	The routine begins by declaring the necessary ADO objects and a string constant containing a semicolon:

Public Function myGetList( _
             strCommandText as String, _
               Optional varParam)
Dim rst as ADODB.Recordset
Dim cmd as ADODB.Command
Dim prm as ADODB.Parameter
Const DELIM as String = ";"

	With the declarations out of the way, the code creates an ADO Command object (only the ADO Command object supports passing parameters to stored procedures). After creating the Command object, the code connects to the MSDE database by setting the Command object’s ActiveConnection property to the value returned by Access’s CurrentProject object’s Connection property:

Set cmd = New ADODB.Command
With cmd
   .ActiveConnection = CurrentProject.Connection

	Since one of the routine’s parameters is Optional, I then check to see if it’s missing by using the IsMissing function (using this function forces me to declare my Optional parameter as a Variant). If the parameter isn’t missing, I create a Parameter object using the Command object’s CreateParameter method. Reading through the parameters to the CreateParameter method, you can see that the parameter I create is to be called Param1 (though I could have called it anything that I want), is a variable character datatype, is to be used only to pass data into the stored procedure, is not to exceed 50 characters, and is set to the value passed as the routine’s varParam parameter. Once the parameter is created, I append it to the Command object’s Parameters collection:

  If Not IsMissing(varParam) Then
     Set prm = .CreateParameter("Param1", _
     adVarChar, adParamInput, 50, varParam)
     Parameters.Append prm
   End If

	With the parameter to my stored procedure set up with the Command object, I set the Command object’s CommandText property to the text that I want to have executed (in this case, the name of the stored procedure passed to the routine in the strCommandText parameter). I also set the CommandType property so that ADO will know that this is a stored procedure:

  .CommandText = strCommandText
   .CommandType = adCmdStoredProc
End With

	Now that I have my Command object set up, I create a recordset and use its Open method to fill the recordset with records I want by passing my fully configured Command object to the Open method:

Set rst = New ADODB.Recordset
rst.Open cmd

	I now have a recordset of the data that I want but, as I said, I can’t pass a recordset to the list box. I can, however, pass a string of values separated by semicolons. Thanks to ADO, that conversion is easy. I first check to see if my recordset is empty by looking at the BOF property. If the property is false, it indicates that I’ve retrieved records, so I then use the recordset’s GetString method. This method converts the recordset’s value into a string consisting of all of the values in the recordset, separated by any characters that I want. There are a lot of parameters that I could have passed to the GetString method, but I chose to specify only the character to separate individual rows. This is specified by the third parameter to the method and I use the Constant semicolon that I declared at the start of the routine. Since I have only one column in each row, this means that I get all the values in the recordset as a single string, delimited by semicolons–the perfect format for the RowSource property of a list box. I return this string as the value of the function:

myGetList = ""
If Not rst.EOF Then
    myGetList = rst.GetString(adClipString,, _
              DELIM,DELIM)
End If

End Function

	In the code behind your form, you must now create a subroutine to be called from the form’s Current event and the control’s AfterUpdate event. That routine’s sole job is to call myGetList function and set the list box’s Row Source property to the string that the function returns:

Private Sub MyListRequery2()
Me.MyListControl.RowSource = _
        myGetList("procCreateMyList", _
        Me.ctlParamSource & "")
End Sub

	Since the varParam argument is optional, you can reuse the myGetList function with stored procedures having no parameters. For simplicity and flexibility’s sake, I made the optional parameter a variable character field that can hold up to 50 characters.

&#9;Suppose you want to create a two-column list for which the first column is a hidden key that will be used to supply a parameter to another query. Also, you want the first row of the list to show the word "All," indicating that a wild card will be passed to the subsequent query. This routine calls a different stored procedure that returns two values per row and prefixes the returned result with "<All>":

   Private Sub MyListControl2Query()
      Me.MyListControl2.RowSource = _ 
            "%;<ALL>;" & myGetList("procCreateMyList2")
    End Sub

&#9;A warning: You shouldn’t use the myGetList function with very long lists, because value lists are limited to 2,047 characters. Also, don’t use the routine if the items in your list contain commas (for example, "LastName, FirstName"). The value list property will interpret the commas as field delimiters. In those cases you’ll have to abandon your stored procedure and use an SQL statement.

Using stored procedures with forms

You can also use stored procedures with your forms. In creating a form when you select either the New button or the "Create Form by Using Wizard" option, you’re presented with a dialog box that asks you to select your record source from a drop-down list of tables and views. Stored procedures aren’t listed, but you shouldn’t take this as a hint that you can’t or shouldn’t use a stored procedure as the record source for your form. You simply can’t use a stored procedure in the wizard.

&#9;If I have a stored procedure that I want to use as the record source for a form, I create a view that mimics the structure of the stored procedure but doesn’t include the parameters. When creating the form, I select that view in the wizard’s list. After the wizard works its magic, I go into the form’s Properties Sheet and change the Record Source property from the view name to the stored procedure name. I also use the stored procedure’s name as the basis for the name of the view, so it’s easy to make the conversion.

&#9;You aren’t limited to using stored procedures without parameters, either. In Access 2000, Forms have an InputParameters property that you can use to pass parameters to the stored procedure to which the form is bound to. As an example, the sample project that accompanies this article is a prototype of a staff payroll application for a telemarketing research company. The main form consists of continuous records of employee work hours and their interview production. The stored procedure for the main form has three parameters: the site of the field office where calls were made, and two date parameters. The procedure looks like this:

Alter Procedure procStaffHoursForm
  @SiteID varChar(50), 
  @DateFrom DateTime, 
  @DateTo DateTime
AS 
SELECT strSiteID, intRecNum, strStaffID, 
   strprojectID, dtmDate, dtmTimeIn, dtmTimeOut,
   intInterviews
FROM tblStaffHours
WHERE strSiteID Like @SiteID and dtmDate 
  Between @DateFrom and @DateTo
ORDER BY intRecNum

&#9;After creating the form using an equivalent view, I set the following properties on the form. The key property is Input Parameters, which I use to specify default values for the parameters required by the stored procedure:

Allow Additions: Yes 
Data Entry: No
Input Parameters:  @SiteID = '%', _
     @DateFrom = Date() , @DateTo = Date()

&#9;In the form’s header I placed a two-column combo box called cboSiteDefault that lists the field site names with their IDs in a hidden column. Then I placed two Date/Time Picker controls on the form to handle the two date parameters (see Figure 1). The code behind the form contains a routine for the Form Load event. This event sets default values for the controls on the form that will, eventually, be used as the inputs to the stored procedure’s parameters. The GetSiteDefaultList uses myGetList to set the combo box’s Row Source so that it will display all of the possible entries and a wild card entry:

Private Sub Form_Load 
   GetSiteDefaultList 
   Me.cboSiteDefault = "%" 
   Me.dtpDateFrom = Date 
   Me.dtpDateTo = Date 
End Sub

Private Sub GetSiteDefaultList() 
  Me.cboSiteDefault.RowSource = _
   "%;All Sites;" & myGetList("procListSite") 



End Sub

&#9;The key routine in this form is called GetRecords. It takes the current values from the controls on the form and formats them into a comma-delimited string. After creating the string, the form’s InputParameters property is passed the string which, in turn, passes the values in the string to the stored procedure’s parameters. I call this routine from the AfterUpdate event for any of the controls that feed the parameters (such as the cboSiteDefault combo box):

Private Sub GetRecords() 
  Form.InputParameters = _
      "'" & Me.cboSiteDefault & _
        "', '" & Me.dtpDateFrom & _
        "', '" & Me.dtpDateTo & "'" 
End Sub

&#9;When the form opens, it loads only the records with today’s date for all field sites. If a user wants to view and edit the records for Boston from the past month, he or she picks Boston from the combo box and today’s date minus a month from the first Date/Time Picker control. The call to GetRecords passes the data to the stored procedure and requeries the form.

&#9;When setting the InputParameters property, you have to pay careful attention to the placement of the single and double quotes. All string values must be enclosed in single quotes, and those single quotes must, in turn, be enclosed in double quotes in the statement that sets the Input Parameters property. You must also make sure that the order of the values that you pass matches the order of the parameters for the stored procedure.

Work with any form

Like the form wizard, the report wizard doesn’t list stored procedures among its available record sources. Once again, do not be misled. Go ahead and create the first draft of your report in the report wizard with a view, then change the record source later to the name of your stored procedure.

&#9;Now suppose that you’re designing a payroll report for the project I described before. In report design you would, as the documentation suggests, set Input Parameters using the bang (!) syntax as follows:

@SiteID=[Forms]![frmStaffHours]![cboSiteDefault],
@DateFrom=[Forms]![frmStaffHours]![dtpDateFrom], 
@DateTo=[Forms]![frmStaffHours]![dtpDateTo]

&#9;You could then call the report from the open staff hours form with a simple DoCmd.OpenReport "rptPayrollHours," provided that the frmStaffHours Form is open. The report would pull the parameters from the staff hours form and create the report.

&#9;Does this mean that you can only call this (or any) report from only one form, the one referenced in the report’s property sheet? Yes, but luckily, there’s a way to decouple the report from the form that calls it, enabling the report to be called from anywhere.

&#9;First, create an unbound form called "frmParam." Place some text boxes on the form (one for each parameter that you might need) and name them "txtParam1," "txtParam2," "txtParam3," and so on. In a standard module, create this public function which, when passed a set of values, updates the text boxes on the frmParam form (opening the form invisibly, if it’s not already open):

Public Sub SendParameters(varParam1, _
        Optional varParam2, Optional varParam3, _
        Optional varParam4, Optional varParam5)

If Not IsLoaded("frmParam") Then 
   DoCmd.OpenForm FormName:="frmParam", _
           WindowMode:=acHidden
End If

With Forms("frmParam")
   .Controls("txtParam1") = varParam1
   If Not IsMissing(varParam2) Then
      .Controls("txtParam2") = varParam2
      If Not IsMissing(varParam3) Then
       .Controls("txtParam3") = varParam3
        If Not IsMissing(varParam4) Then
           .Controls("txtParam4") = varParam4
           If Not IsMissing(varParam5) Then
             .Controls("txtParam5") = varParam5
           End If 
        End If
      End If
   End If
End With
End Sub

&#9;In the payroll report, I set its Input Parameters properties to pull its information from the first three fields on the frmParam form:

@SiteID = [Forms]![frmParam]![txtParam1], 
@DateFrom = [Forms]![frmParam]![txtParam2], 
@DateTo = [Forms]![frmParam]![txtParam3]

&#9;All that’s left is to tie the staff hours form (or any other form) to the frmParam form. To do that, I create a command button with the following code to call the SendParameters routine and then open my report:

Private Sub cmdPayrollReport_Click() 
Me.Dirty = False  
SendParameters Me.cboSiteDefault, _
               Me.dtpDateFrom, Me.dtpDateTo 
DoCmd.OpenReport "rptPayrollHours" 
End Sub

&#9;Using SendParameters you can pass parameters from any source to any report, or form, by way of the hidden form, frmParam.

&#9;The Access Data Project is really a "Version 1" product; subsequent releases will unveil its full power. Finding your way around it can be a bit frustrating due to limited documentation and some misleading features. As you explore ADP, you should plan on getting all the help you can from third-party Access 2000 books, as well as publications, like this one. If you persist, you’ll soon be able to develop lean and fast front ends to enterprise databases.

Burton Roberts and his wife, Jennifer, are principals in Extended Day Services, a provider of school-aged childcare for school districts in the South Hills of Pittsburgh, PA. As an independent consultant, Burton has authored applications for businesses in occupational health and specialty steel. Burton is an Access MCP and has an MBA from Carnegie Mellon University. bhroberts@adelphia.net. (412) 608-4205.

Most server databases (for example, Microsoft SQL Server, Oracle, and the MSDE that ships with Access 2000) support the creation of stored procedures. Stored procedures are programs stored in the database that you can call by name to execute. Stored procedures act, in many ways, like VBA functions: They can accept parameters and then return data values through output parameters or recordsets. You also use them to run "Action" queries. Because they’re stored in the database, you gain two benefits from using stored procedures: precompiled queries and reduced network traffic.

&#9;Your VBA code might contain several SQL statements. By putting them in a stored procedure, the database will perform all the parsing, compiling, and planning required to run the query before you call it. As a result, when you call the stored procedure, those steps are skipped and the query is executed. Depending on the complexity of your query, this can save a significant amount of time. A stored procedure can also contain If and Do While statements (or their equivalents). This allows you to transfer not only your SQL statements, but also the control code around those statements to a stored procedure. Instead of making repeated network calls to your server database, you can simply call the stored procedure.

In Access Projects you create MSDE or SQL Server stored procedures by selecting ‘New’ from the Stored Procedures section of the database container. The template starts with the line:

Create Procedure 'StoredProcedure1'

&#9;You should replace ‘StoredProcedure1’ with the name that you want to assign to your stored procedure, then save and close it. When you reopen the stored procedure in design mode and you’ll see that first line has been changed to Alter Procedure and the single quotes have been dropped from the stored procedure name.

&#9;If your stored procedure contains complex Select queries, it’s a good idea to use the View designer to get started. You can join tables and type in aliases in the same manner that you do in Access MDB files. Views don’t accept parameters and don’t support the ORDER BY clause. Once you have the query that you want, click the SQL button on the toolbar to see the SQL statement for the view. Copy the SQL and paste it to your new stored procedure after "/* set nocount on */" in the template but before "return." Now you can add your parameters and any ORDER BY clauses that you need after the WHERE clause.

&#9;You can also use the saved views that you create from the SQL code of your stored procedure:

SELECT * FROM vwViewName ORDER BY fieldname

To find out more about Smart Access and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57

Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.

This article is reproduced from the June 2000 issue of Smart Access. Copyright 2000, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Smart Access is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-493-4867 x4209.