Microsoft KB Archive/239946

= BUG: ADODB Jet 4.0 Recordset.Filter with Server Cursor Fails in Windows 95 and Windows 98 =

Article ID: 239946

Article Last Modified on 8/23/2001

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.01
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2

-



This article was previously published under Q239946



SYMPTOMS
When you attempt to use a Filter on a Server cursor with a Microsoft Access table that contains a column Default Value = "", no row is found for the comparison. For example, Recordset.Filter = "myString = ''" fails on Microsoft Windows 95 and Microsoft Windows 98 if the Access table has a column Default Value = "" (empty string). This comparison works correctly with Microsoft Windows NT.



RESOLUTION
To resolve this problem, do one of the following:
 * Use a Client cursor.
 * Do not use an empty string default for the Access table column. Instead, leave the Default Value property blank and set Allow Zero Length equal to Yes. As a result of this action, a Null is entered by default, which works correctly in the Filter comparison for Windows 95, Windows 98, and Windows NT.



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.



Step to Reproduce the Problem
These steps will set up the test data:  Create a new Access 2000 database in the same folder as the Microsoft Visual Basic project file in step 5, with a default name of Db1.mdb. Create a new table in the Access database, with a default name of Table1. Create a column named myString in the table with Default Value = "". Set Allow Zero Length to Yes. Create a row of data in the table but do not enter data for the second column.  Create a new Visual Basic Standard EXE project and paste the following code in the Form-General Declarations section. Set a reference to the ActiveX Data Objects Library. Private Sub Form_Click

Dim adoCn As ADODB.Connection Dim adoRs As ADODB.Recordset Dim strCn As String

strCn = App.Path & "\db1.mdb"

Set adoCn = New ADODB.Connection With adoCn .Provider = "Microsoft.JET.OLEDB.4.0" .Open strCn, "Admin", "" End With

Set adoRs = New ADODB.Recordset With adoRs Set .ActiveConnection = adoCn .LockType = adLockPessimistic .CursorLocation = adUseClient .CursorType = adOpenKeyset End With

adoRs.Open "Table1",, , , adCmdTableDirect

adoRs.Filter = "myString = ''"

If adoRs.EOF Or adoRs.BOF Then Form1.Caption = "EOF" MsgBox "Empty Recordset...", vbOKOnly Else Form1.Caption = adoRs.Fields(0).Name MsgBox "Not Empty Recordset...", vbOKOnly End If

adoRs.Close adoCn.Close

Set adoRs = Nothing Set adoCn = Nothing

End Sub </ol>

To demonstrate the problem, run the code on a computer that is running Windows 95 or Windows 98. You see Recordset.EOF. On Windows NT, the Filter works correctly.

<div class="references_section">