Microsoft KB Archive/286584

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

Article ID: 286584

Article Last Modified on 5/9/2003

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.5
 * Microsoft Access 2002 Standard Edition

-



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.



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 = &quot;Microsoft.Jet.OLEDB.4.0&quot; .Properties(&quot;Data Source&quot;).Value = _ &quot;C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb&quot; .Open End With

Set rs = New ADODB.Recordset With rs       .ActiveConnection = cn        .Source = &quot;SELECT * FROM Customers&quot; .CursorType = adOpenKeyset .CursorLocation = adUseClient .LockType = adLockOptimistic .Open .Filter = &quot;Country = 'USA'&quot; Debug.Print &quot;Recordcount: &quot; & .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 = &quot;ALTER TABLE Customers ALTER COLUMN &quot; & _ &quot;Country VARCHAR(15)&quot; Set cn = New ADODB.Connection With cn     .Provider = &quot;Microsoft.Jet.OLEDB.4.0&quot; .Properties(&quot;Data Source&quot;).Value = _ &quot;C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb&quot; .Open .Execute strSQL

'Update the Country field to strip out the trailing spaces. strSQL = &quot;UPDATE Customers SET Country = RTrim([Country])&quot; .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.



Steps to Reproduce the Behavior
 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.

 Start Microsoft Access, and then create a new, blank database. On the Insert menu, click Module. On the Tools menu, click References. Click to clear the Microsoft ActiveX Data Objects 2.1 Library check box.</li> Click to select the Microsoft ActiveX Data Objects 2.5 Library check box, and then click OK.</li>  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 = &quot;Microsoft.Jet.OLEDB.4.0&quot; .Properties(&quot;Data Source&quot;).Value = _ &quot;C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb&quot; .Open End With

'Alter the Customers table to change the Country column from 'variable-length to fixed-length. cn.Execute &quot;ALTER TABLE Customers ALTER COLUMN Country CHAR(15)&quot; Set rs = New ADODB.Recordset With rs       .ActiveConnection = cn        .Source = &quot;SELECT * FROM Customers&quot; .CursorType = adOpenKeyset .LockType = adLockOptimistic .Open .Filter = &quot;Country = 'USA'&quot; Debug.Print &quot;Recordcount: &quot; & .RecordCount .Close End With cn.Close Set rs = Nothing Set cn = Nothing End Sub </li> On the View menu, click Immediate Window.</li>  Type the following line into the Immediate window, and then press ENTER: ADOFilterTest </li></ol>

Note that RecordCount returns 0, even though there are 13 records that match this criteria.

Additional query words: pra doesn't

Keywords: kbbug kbnofix KB286584

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.