Access queries with a wildcard character do not export data to an XML document


BUG #: 8808 (Content Maintenance)BUG #: 232596 (OfficeNet) This article applies only to a Microsoft Access database (.mdb).


Moderate: Requires basic macro, coding, and interoperability skills.

Symptoms


When you export an Access query to an XML document, and the query contains a Microsoft Jet wildcard character such as an asterisk (*) in the criteria, the data is not exported to the XML document. Therefore, the resultant XML document does not contain any data.

The query functions correctly if you run the same query by using Access.

Cause


This behavior occurs when you export to XML. XML uses ActiveX Data Objects (ADO) to query the data from the database. When you use a Jet-specific wildcard character in the Access query, the query returns no records. This problem occurs because ADO recognizes only American National Standards Institute (ANSI) 92 wildcard characters.

Workaround


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

Method 1

You can rewrite the query to replace the Jet-specific wildcard character with the ANSI 92 wildcard character. For example, the original query may look similar to this with the * wildcard character:
SELECT Customers.CustomerID
FROM Customers
WHERE (((Customers.CustomerID) Like "A*"));
You can rewrite the query as follows:
SELECT Customers.CustomerID
FROM Customers
WHERE (((Customers.CustomerID) Like "A%"));
Note If the Access database is not configured for ANSI 92 compatibility, when you run the rewritten query from Access, no data is returned. However, the exported XML document that is based on the rewritten query returns the expected results.

Method 2

You can rewrite the query so that the criterion of the query does not contain wildcard characters. For example, the original query may look similar to this with the * wildcard character:
SELECT Customers.CustomerID
FROM Customers
WHERE (((Customers.CustomerID) Like "A*"));
You can rewrite the query as follows:
SELECT Customers.CustomerID
FROM Customers
WHERE Left(CustomerID,1) = "A";

Method 3

When you export data to an XML document, you can specify whether to apply an existing filter on the object. Instead of specifying a wildcard character in the criteria of the query, you can create a filter with the filter string equivalent to the criteria that you want (such as "A*") and then apply this filter during the Export XML process.

To do this, follow these steps, depending on the version of Access.

Access 2003

  1. Open the Northwind sample database.
  2. On the Insert menu, click
    Query.
  3. In the New Query dialog box, select
    Design View, and then click OK.
  4. On the View menu, click SQL View.
  5. In the Query window, paste the following query:
    SELECT Customers.CustomerID
    FROM Customers;
    Note You can see that the criterion of the query is removed. Therefore, the query does not contain any wildcard characters.
  6. On the File menu, click
    Save.
  7. In the Query Name box of the
    Save As dialog box, type
    Query1, and then click OK.
  8. On the View menu, click
    Datasheet View.
  9. On the Records menu, point to
    Filter, and then click Advanced Filter/Sort.
  10. In the Field list, select
    CustomerID.
  11. In the Criteria box under
    CustomerID, paste the following code:
    Like "A*"
  12. On the Filter menu, click
    Apply Filter/Sort.
  13. On the File menu, click
    Save.
  14. On the File menu, click
    Close.
  15. In the Database window, click
    Queries.
  16. Right-click Query1, and then click
    Export.
  17. In the Export Query 'Query1' To dialog box, select XML in the Save as type list, and then click Export.
  18. In the Export XML dialog box, click
    More Options.
  19. In the Export XML dialog box, select the Data tab. Under Records To Export, click to select the Apply existing filter check box, and then click
    OK.

Access 2007

  1. Open the Northwind sample database.
  2. On the Create tab, click Query Design, and then click Close.
  3. On the Design tab, click SQL View in the Results group.
  4. In the Query window, paste the following query:
    SELECT Customers.[First Name]
    FROM Customers;
    Note You can see that the criterion of the query is removed. Therefore, the query does not contain any wildcard characters.
  5. Click the Microsoft Office Button, and then click Save.
  6. In the Query Name box of the
    Save As dialog box, type
    Query1, and then click OK.
  7. On the Home tab, in the Views group, click View, and then click Datasheet View.
  8. On the Home tab, click Advanced in the Sort & Filter group, and then click Advanced Filter/Sort.
  9. In the Field list, select First Name
  10. In the Criteria box under
    First Name, paste the following code:
    Like "A*"
  11. On the Home tab, click Advanced in the Sort & Filter group, and then click Apply Filter/Sort.
  12. Click the Microsoft Office Button, and then click Save.
  13. Right-click Query1, point to Export, click XML File, and then click OK.
  14. In the Export XML dialog box, click
    More Options.
  15. In the Export XML dialog box, click the Data tab.
  16. Under Records To Export, select the Apply existing filter option, and then click
    OK.
  17. In the Export - XML File dialog box, click Close.

Status


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

More Information


Steps to reproduce the problem in Access 2003

  1. Open the Northwind sample database.
  2. On the Insert menu, click
    Query.
  3. In the New Query dialog box, select
    Design View and then click OK.
  4. On the View menu, click SQL View.
  5. In the Query window, paste the following query:
    SELECT Customers.CustomerID
    FROM Customers
    WHERE (((Customers.CustomerID) Like "A*"));
  6. Click Save on the Filemenu.
  7. In the Query Name box that is in the
    Save As dialog box, type
    Query1.
  8. On the File menu, click
    Close.
  9. Right-click Query1 and then click Export.
  10. In the Export Query 'Query1' To... dialog box, select XML in the Save as type list and then click Export.
  11. In the Export XML dialog box, click
    OK to save the XML-related files in the default location.
  12. In Microsoft Internet Explorer, open the exported XML file.

    You can see that the exported XML file that is based on the specified query does not contain any records.

References


For more information about ANSI 92 syntax, type
about ansi sql query mode in the Office Assistant and then click Search.