Help and Support

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

View products that this article applies to.
Article ID:181716
Last Review:March 2, 2005
Revision:1.1
This article was previously published under Q181716
On This Page

SYMPTOMS

When using the ADO Parameters.Refresh method with a text datatype parameter on a parameterized Microsoft Access 97 QueryDef, error 3708 may occur:
The application has improperly defined a Parameter Object.
In version 2.6, the error message is:
Parameter object is improperly defined, inconsistent or incomplete information was provided.
This occurs because a size of zero (0) is returned on the text datatype parameter by the ADO Refresh method.

This article has 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.

Back to the top

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.

Back to the top

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.

NOTE for ADO 2.1 and later versions: The REFRESH method does NOT return information about the .Name or .Type properties of the Parameters collection as it does in ADO 2.0. Therefore, it is necessary to assign a value to the .Type property. If you need to reference the parameter by name, you also need to assign a value to the .Name property.

Back to the top

STATUS

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

Back to the top

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.

Back to the top

REFERENCES

For information on creating a Parameter collection for an Access 97 Querydef please see the following article in the Microsoft Knowledge Base:
181782 (http://support.microsoft.com/kb/181782/EN-US/) HOWTO: Work with Access Querydef Parameter Using VB
Microsoft Data Access Components (MDAC) contains ADO, which is needed for this article. For more information on MDAC, please see the following article in the Microsoft Knowledge Base:
175018 (http://support.microsoft.com/kb/175018/EN-US/) HOWTO: Acquire and Install the Microsoft Oracle ODBC Driver

Back to the top

APPLIES TO
•Microsoft ActiveX Data Objects 1.5
•Microsoft ActiveX Data Objects 2.0
•Microsoft ActiveX Data Objects 2.1 Service Pack 2
•Microsoft ActiveX Data Objects 2.5
•Microsoft ActiveX Data Objects 2.6
•Microsoft ActiveX Data Objects 2.7

Back to the top

Keywords: 
kbcode kbdatabase kbprb KB181716

Back to the top

Article Translations

 

Other Support Options

  • Contact Microsoft
    Phone Numbers, Support Options and Pricing, Online Help, and more.
  • Customer Service
    For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
  • Newsgroups
    Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.