HOWTO: Work w/ Microsoft Access QueryDef Parameter Using VB 5.0

Last reviewed: March 2, 1998
Article ID: Q181782
The information in this article applies to:
  • ActiveX Data Objects (ADO), version 1.5

SUMMARY

This article contains examples that illustrate how to call a Microsoft Access 97 parameterized QueryDef and return an ADO recordset using Visual Basic version 5.0. Examples are given for both text and numeric input parameters.

MORE INFORMATION

The following examples illustrate ways to return an ADO recordset from a Microsoft Access QueryDef, which expects a parameter in order to return a recordset of one or more rows. The sample application contains three procedures that use Microsoft Access 97 QueryDefs that use parameters.

The first procedure shows an example of passing a numeric parameter while the second procedure shows an example of passing a text parameter. The first two procedures show how to create an ADO parameter collection and define all the parameter properties to have an ADO recordset returned. The third procedure shows how to use the ADO Parameters.Refresh method to return the properties of a parameter.

NOTE: You need to acquire and install the Microsoft Data Access Components (MDAC) version 1.5 stack for the sample in this article.

The first requirement is to create the Microsoft Access 97 QueryDefs. This article assumes the reader is familiar with creating new QueryDefs in Microsoft Access 97. The following Visual Basic (VB) 5.0 code expects two new QueryDefs in the sample Nwind.mdb included with VB 5.0. Create the new QueryDefs with the following properties:

   Query Name    Table     Criteria      On Field      Datatype
   ------------------------------------------------------------

   ProductsByID  Products  [ProductID]   ProductID     Integer
   CustomerByID  Customers [CustomerID]  CustomerID    Text

Make sure you also set the parameter name and datatype in Microsoft Access 97 by selecting Query, and then click Parameters.

After the Microsoft Access 97 QueryDef's are created, open a new Visual Basic 5.0 Project. Form1 is created by default. Under Project, References select Microsoft ActiveX Data Objects 1.5 Library.

Follow these steps:

  1. Add three Command buttons to the new form with these settings:

          Button      Name           Caption
          ----------------------------------
    
          Command1    cmdNumeric     Numeric Parameter
    
          Command2    cmdText        Text Parameter
    
          Command3    cmdParameters  Determine Parameter Properties
    
    

  2. Paste the following code into the Form1 General Declarations section:

          Dim Conn As New ADODB.Connection
          Dim Cmd As New ADODB.Command
          Dim Cmd1 As New ADODB.Command
          Dim Cmd2 As New ADODB.Command
          Dim Rs As New ADODB.Recordset
    

            Private Sub Form_Load()
              Dim strConn As String
    
              'Change the DSN to match your settings.
                strConn = "DSN=Access97;"
                With Conn
                    .CursorLocation = adUseClient
                    .ConnectionString = strConn
                    .Open
                End With
    
            End Sub
    
            Private Sub cmdNumeric_Click()
             'Passes a Numeric parameter to a Microsoft  Access 97 QueryDef
             'that is based on the Products table. The parameter is on the
             'ProductID field.
    
             With Cmd
               Set .ActiveConnection = Conn
                .CommandText = "Productsbyid"
                .CommandType = adCmdStoredProc
    
                'ADO Numeric Datatypes are very particular
                .Parameters.Append .CreateParameter("paramCustID", _
                                                   adSmallInt, _
                                                   adParamInput, _
                                                   2) 'Works without a Size
             End With
    
             Cmd.Parameters("paramCustID") = 3
               'OR
             'Cmd.Parameters(0) = 3
             Rs.Open Cmd, , adOpenStatic, adLockReadOnly
    
             Debug.Print Rs(0), Rs(1), Rs(2)
             Rs.Close
          End Sub
    
          Private Sub cmdText_Click()
           'Passes a Text parameter to a Microsoft Access 97 QueryDef that
           'is based on the Customers table. The parameter is on the
           'CustomerID field.
    
             With Cmd1
               Set .ActiveConnection = Conn
               .CommandText = "Customerbyid"
               .CommandType = adCmdStoredProc
    
               'Can use either adVarChar or adChar dataType
               .Parameters.Append .CreateParameter("paramCustID", _
                                                    adVarChar, _
                                                    adParamInput, _
                                                    5) 'needs Size to work
             End With
    
             Cmd1.Parameters("paramCustID") = "COMMI"
             Rs.Open Cmd1, , adOpenStatic, adLockReadOnly
    
              Debug.Print Rs(0), Rs(1), Rs(2)
              Rs.Close
          End Sub
    
          Private Sub cmdParameters_Click()
           'The purpose of this procedure is to determine the
           'properties of a parameter.
           '
           With Cmd2
               Set .ActiveConnection = Conn
               .CommandText = "ProductsbyID"
               .CommandType = adCmdStoredProc
           End With
           Cmd2.Parameters.Refresh
    
           Debug.Print "The parameter properties for CustomerbyID are: " _
            & vbCrLf _
            & "Name: " & Cmd2.Parameters(0).Name & vbCrLf _
            & "Type: " & Cmd2.Parameters(0).Type & vbCrLf _
            & "Direction: " & Cmd2.Parameters(0).Direction & vbCrLf _
            & "Size: " & Cmd2.Parameters(0).Size
    
            Debug.Print "-------------"
    
           With Cmd2
               Set .ActiveConnection = Conn
               .CommandText = "CustomerbyID"
               .CommandType = adCmdStoredProc
           End With
           Cmd2.Parameters.Refresh
    
           Debug.Print "The parameter properties for CustomerbyID are: " _
            & vbCrLf _
            & "Name: " & Cmd2.Parameters(0).Name & vbCrLf _
            & "Type: " & Cmd2.Parameters(0).Type & vbCrLf _
            & "Direction: " & Cmd2.Parameters(0).Direction & vbCrLf _
            & "Size: " & Cmd2.Parameters(0).Size
    
          End Sub
    
    
Run the project and notice the results of each button click.

You may have noticed that another way to get a recordset back from a Microsoft Access QueryDef is to use the Parameter.Refresh method rather than defining the parameter properties in a parameter collection. This actually works although an extra round trip is necessary to the server. However, when passing a text parameter an error occurs because the correct size of a text parameter is not returned by invoking the Parameter.Refresh method. A size property is required when passing a text parameter. This property can be set before creating the ADO recordset to avoid the error.

REFERENCES

For additional information on MDAC 1.5, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q175018
   TITLE     : HOWTO: Acquire and Install the Microsoft Oracle ODBC Driver
               v2.0


Additional query words: vbwin
Keywords : adovb adoaccess
Version : WINDOWS:1.5
Platform : WINDOWS
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: March 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.