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

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.

MORE INFORMATION

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

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.