PRB: ADO Parameter Refresh Err with MS Access 97 Querydef & VBID: Q181716
|
When using the ADO Parameters.Refresh method with a text datatype parameter on a parameterized Microsoft Access 97 QueryDef, error 3708 may occur:
This occurs because a size of zero (0) is returned on the text datatype parameter by the ADO Refresh method.The application has improperly defined a Parameter Object.
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.
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.
Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.
Query Name Table Criteria On Field Datatype
-------------------------------------------------------------------
ProductsByID Products [ProductID] ProductID Integer
CustomerByID Customers [CustomerID] CustomerID Text
Button Name Caption
-------------------------------------
Command1 Command1 ProductsByID
Command2 Command2 CustomerByID
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
For information on creating a Parameter collection for an Access 97
Querydef please see the following article in the Microsoft Knowledge Base:
Q181782 HOWTO: Work with Access Querydef Parameter Using VB
Q175018 HOWTO: Acquire and Install the Microsoft Oracle ODBC Driver
Additional query words: ado Access vbwin kbdse kbsweepnext kbcodesam
Keywords : kbcode kbADO150 kbADO200 kbDatabase
Version : WINDOWS:1.5,2.0
Platform : WINDOWS
Issue type : kbprb
Last Reviewed: May 3, 1999