PRB: ADO Parameter Refresh Err with MS Access 97 Querydef & VB

Last reviewed: February 27, 1998
Article ID: Q181716
The information in this article applies to:
  • ActiveX Data Objects (ADO), version 1.5

SYMPTOMS

When using the ADO Parameters.Refresh method with a text datatype parameter on a parameterized Microsoft Access 97 QueryDef, error 3708 listed below may occur:

   The application has improperly defined a Parameter Object.

This occurs because a size of zero (0) is returned on the text datatype parameter by the ADO Refresh method.

This article gives an example of how to use the ADO Refresh method on a parameterized Microsoft Access 97 QueryDef. Using the Refresh method does result in a round trip to the server so if performance is important it would be better to create a Parameter collection and define the parameter properties in code.

CAUSE

The ADO Parameters.Refresh method does not return the actual parameter Size property, the method returns a value of 0. Microsoft Access's parameter syntax does not allow for the size of the parameter to be set in the QueryDef. Setting the Size property of a text datatype parameter to 0 or a value less than the actual size of the text field results in an error when executing the ADO command that passes this parameter.

RESOLUTION

To work around this error it is necessary, in your code, to set the text datatype parameter .Size property to a value equal to or greater than the actual field size. The following example shows how to define the Size property when working with a text datatype parameter.

STATUS

Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

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 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 that you are familiar with creating new QueryDefs in Microsoft Access 97. The following Visual Basic 5.0 code expects two new QueryDefs in the sample Nwind.mdb included with Visual Basic version 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 you set the parameter name and datatype in Microsoft Access 97 under Query Parameters. Create each query with three fields.

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

  1. Under Project, References, select the Microsoft ActiveX Data Objects 1.5 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 Form1 General Declarations section:

          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) = 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) = "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.

REFERENCES

For information on creating a Parameter collection for an Access 97 Querydef please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q181782
   TITLE     : HOWTO: Work with Access Querydef Parameter Using VB 5.0

Microsoft Data Access Components (MDAC) 1.5 contains ADO version 1.5 that is needed for this article. For more information on MDAC, 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: ado Access vbwin
Keywords : kbcode adovb adoaccess
Version : WINDOWS:1.5
Platform : WINDOWS
Issue type : kbprb


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: February 27, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.