ACC2002: ADO Filter Does Not Work with Fixed-Length Fields When You Use Microsoft Jet OLE DB Provider (286584)


The information in this article applies to:

  • ActiveX Data Objects (ADO) 2.5
  • Microsoft Access 2002
This article was previously published under Q286584
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SYMPTOMS

When you set the Filter property of an ActiveX Data Objects (ADO) recordset that is opened with the Microsoft Jet OLE DB provider, no records are returned.

CAUSE

You are filtering a fixed-length column, and you have MDAC 2.5 installed on the computer.

RESOLUTION

Install MDAC 2.6 on the Computer

This problem has been corrected in MDAC 2.6. To resolve this problem, upgrade your computer to MDAC 2.6. You can obtain the latest version of MDAC at the following Microsoft Web site:

http://msdn.microsoft.com/dataaccess

To temporarily work around this problem, use one of the following methods.

Use a Client-Side Cursor

You can work around this problem by opening the ADO recordset as a client-side cursor rather than as a server-side cursor. To open the recordset as a client-side cursor, set the CursorLocation property of the recordset to the constant adUseClient before you open it. The following is a code sample that opens the recordset as a client-side cursor: 
Sub ADOFilterTest()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Set cn = New ADODB.Connection
    With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Data Source").Value = _
         "C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb"
        .Open
    End With

    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = cn
        .Source = "SELECT * FROM Customers"
        .CursorType = adOpenKeyset
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Open
        .Filter = "Country = 'USA'"
        Debug.Print "Recordcount: " & .RecordCount
        .Close
    End With
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub

Use a Variable-Length Text Column

You can also work around this problem by using a variable-length text column in your table instead of a fixed-length text column. You can use a SQL Data Definition Language (DDL) query to accomplish this. The following sample code demonstrates how to alter a column's definition so that it is a variable-length text column: 
Sub AlterColumn()
   Dim cn As ADODB.Connection
   Dim strSQL As String

   'SQL statement to change Country column to a
   '15 character variable-length text column.
   strSQL = "ALTER TABLE Customers ALTER COLUMN " & _
            "Country VARCHAR(15)"   
   Set cn = New ADODB.Connection
   With cn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Properties("Data Source").Value = _
         "C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb"
      .Open
      .Execute strSQL

      'Update the Country field to strip out the trailing spaces.
      strSQL = "UPDATE Customers SET Country = RTrim([Country])"
      .Execute strSQL
      .Close
   End With
   Set cn = Nothing
End Sub

STATUS

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

MORE INFORMATION

Steps to Reproduce the Behavior

  1. CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

  2. Start Microsoft Access, and then create a new, blank database.
  3. On the Insert menu, click Module.
  4. On the Tools menu, click References.
  5. Click to clear the Microsoft ActiveX Data Objects 2.1 Library check box.
  6. Click to select the Microsoft ActiveX Data Objects 2.5 Library check box, and then click OK.
  7. Add the following code to the module:
    Sub ADOFilterTest()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Set cn = New ADODB.Connection
    With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Data Source").Value = _
         "C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb"
        .Open
    End With

    'Alter the Customers table to change the Country column from
    'variable-length to fixed-length.
    cn.Execute "ALTER TABLE Customers ALTER COLUMN Country CHAR(15)"
    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = cn
        .Source = "SELECT * FROM Customers"
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open
        .Filter = "Country = 'USA'"
        Debug.Print "Recordcount: " & .RecordCount
        .Close
    End With
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub
  8. On the View menu, click Immediate Window.
  9. Type the following line into the Immediate window, and then press ENTER:
    ADOFilterTest
Note that RecordCount returns 0, even though there are 13 records that match this criteria.
Modification Type:MajorLast Reviewed:5/9/2003
Keywords:kbbug kbnofix KB286584
©2002 Microsoft Corporation. All rights reserved.