How To Work w/ Microsoft Access QueryDef Parameter Using VB

Exención de responsabilidades de contenido KB retirado

Este artículo se refiere a productos para los que Microsoft ya no ofrece soporte técnico. Por tanto, el presente artículo se ofrece "tal cual" y no será actualizado.

Summary

This article contains examples that illustrate how to call a Microsoft Access parameterized QueryDef and return an ADO recordset using Visual Basic version 6.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 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) for the sample in this article.

If you are using Access 2000 to test, you must use the Jet Engine 4.0 supplied with Microsoft Data Access Components 2.1 and later. You can download the latest version at the following Microsoft Web site:


The first requirement is to create the Microsoft Access QueryDefs. This article assumes the reader is familiar with creating new QueryDefs in Microsoft Access. The following Visual Basic code expects two new QueryDefs in the sample Nwind.mdb included with Visual Basic. 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 by selecting Query, and then choose Parameters.

After the Microsoft Access QueryDef's are created, open a new Visual Basic Project. Form1 is created by default. From the Project menu, choose References then select Microsoft ActiveX Data Objects 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 General Declarations section of Form1:
       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=dsnAccess;"
    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("paramProdID", _
    adSmallInt, _
    adParamInput, _
    2) 'Works without a Size
    End With

    Cmd.Parameters("paramProdID") = 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 ProductsbyID 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, noting 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, please see the following Microsoft Web site:

Propiedades

Id. de artículo: 181782 - Última revisión: 18 jul. 2012 - Revisión: 1

Comentarios