FIX: MDAC 2.1 SP1 Problem Executing Access Query with Wildcard

Article translations Article translations
Article ID: 245343 - View products that this article applies to.
This article was previously published under Q245343
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

SYMPTOMS

In Access the asterisk(*) is the normal wildcard character. However, in Visual Basic with MDAC 2.1 SP1 (version 2.1.1.3711.11(GA)) installed, when calling an Access query using this wildcard character through ADO and the Access ODBC driver, it fails to return any records.

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. This bug is corrected in MDAC 2.1 SP2 (2.1.2.4202.3 (GA)), which can be downloaded from here:

http://msdn.microsoft.com/en-us/data/aa937729.aspx

MORE INFORMATION

Steps to Reproduce Behavior

Create the Access Query

In Access 97 or Access 2000, create a query, Query1, with the following SQL statement in the Nwind.mdb or NorthWind.mdb database:
Select * From Customers Where Customer ID LIKE 'A*'
				
This should return all of the customer records (4 in all) with a Customer ID that starts with "A".

Visual Basic Test Application

  1. Start a new project in Visual Basic and choose Standard EXE. Form1 is created by default.
  2. Using the Project and References menu, add a reference to the following type library:
    Microsoft ActiveX Data Objects 2.1 Library
  3. Add a Command button (Command1) and the following code to the form:
    Option Explicit
    
    Private Sub Command1_Click()
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, cmd As ADODB.Command
    
      Set cn = New ADODB.Connection
      Set cmd = New ADODB.Command
      Set rs = New ADODB.Recordset
    
      cn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
              "DBQ=C:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb"
    
      With cmd
        .Name = "Query1"
        .CommandText = "Query1"
        .CommandType = adCmdStoredProc
        Set .ActiveConnection = cn
      End With
    
      rs.CursorLocation = adUseClient
      rs.Open cmd
      Debug.Print rs.RecordCount
    
      rs.Close
      cn.Close
    
    End Sub
    						
    You need to change the path to Nwind.mdb in the connect string to point to the database you modified.
  4. Run the application and click the Command button. On systems with older versions of MDAC 2.1, 0 is returned as the record count. With the later version of MDAC 2.1, the correct number, 4, is returned.

Properties

Article ID: 245343 - Last Review: February 28, 2014 - Revision: 2.3
APPLIES TO
  • Microsoft ActiveX Data Objects 2.1 Service Pack 1
  • Microsoft ActiveX Data Objects 2.1 Service Pack 2
  • Microsoft Visual Basic 5.0 Enterprise Edition
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
Keywords: 
kbnosurvey kbarchive kbbug kbfix kbmdac250fix kbmdacnosweep KB245343

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com