PRB: ADO Parameter Refresh Error with Access 97 Querydef and Visual Basic

MORE INFORMATION

Steps to Reproduce Behavior

The following example shows ways to return an ADO recordset from a Microsoft Access QueryDef that expects a parameter to return a recordset of one or more rows. The sample application has two procedures that use Microsoft Access 97 parameterized QueryDefs.

The first procedure shows an example of passing a numeric parameter while the second procedure shows an example of passing a text parameter. These two procedures show how using the ADO Parameters.Refresh method retrieves Parameter properties and returns an ADO recordset.

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

The first requirement is to create the Microsoft Access 97 QueryDefs. This article assumes that you are familiar with creating new QueryDefs in Microsoft Access 97. 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 97. From the Query menu, choose Parameters. Create each query with three fields.

After the Microsoft Access 97 QueryDef's are created, open a new Visual Basic Project. Form1 is created by default.

1. From the Project menu, choose References, then select Microsoft ActiveX Data Objects Library.
2. Add two Command buttons to Form1 with these settings:

   
         Button      Name         Caption
         -------------------------------------
   
         Command1    Command1     ProductsByID
         Command2    Command2     CustomerByID
   					

3. Paste the following code into the General Declarations section of Form1:

   
      Dim Conn As New ADODB.Connection
      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
   
             strConn = "DSN=Access97;"
             With Conn
                .CursorLocation = adUseClient
                .ConnectionString = strConn
                .Open
             End With
   
      End Sub
   
      Private Sub Command1_Click()
             With Cmd1
                Set .ActiveConnection = Conn
                .CommandText = "ProductsByID"
                .CommandType = adCmdStoredProc
   
             End With
   
             Cmd1.Parameters.Refresh
             Cmd1.Parameters(0).Type = adInteger 
             Cmd1.Parameters(0) = 3 'Set the numeric parameter value.
   
             Rs.Open Cmd1, , adOpenStatic, adLockReadOnly
             Debug.Print Rs(0), Rs(1), Rs(2)
             Rs.Close
   
      End Sub
   
      Private Sub Command2_Click()
   
             With Cmd2
                 Set .ActiveConnection = Conn
                .CommandText = "CustomerByID"
                .CommandType = adCmdStoredProc
   
             End With
   
             Cmd2.Parameters.Refresh
             Cmd2.Parameters(0).Type = adVarChar
             Cmd2.Parameters(0) = "COMMI"    'Set the text parameter value.
   
             ' If the next line is omitted you will get an error 3708 -
             ' "The application has improperly defined a Parameter Object".
             Cmd2.Parameters(0).Size = 5
   
   
             Rs.Open Cmd2, , adOpenStatic, adLockReadOnly
             Debug.Print Rs(0), Rs(1), Rs(2)
             Rs.Close
   
      End Sub
   					

   Run the example and click the CustomerByID button. The Debug window displays the returned values. Comment the Cmd2.Parameters(0).Size = 5 line and rerun the example. The error occurs.