Microsoft KB Archive/823224

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

Article ID: 823224

Article Last Modified on 4/3/2007

-

APPLIES TO


 * Microsoft Office Access 2007
 * Microsoft Office Access 2003

-



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 &quot;A*&quot;)); You can rewrite the query as follows: SELECT Customers.CustomerID FROM Customers WHERE (((Customers.CustomerID) Like &quot;A%&quot;)); 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 &quot;A*&quot;)); You can rewrite the query as follows: SELECT Customers.CustomerID FROM Customers WHERE Left(CustomerID,1) = &quot;A&quot;;

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 &quot;A*&quot;) and then apply this filter during the Export XML process.

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

Access 2003
 Open the Northwind sample database. On the Insert menu, click Query. In the New Query dialog box, select Design View, and then click OK. On the View menu, click SQL View.  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.  On the File menu, click Save.</li> In the Query Name box of the Save As dialog box, type Query1, and then click OK.</li> On the View menu, click Datasheet View.</li> On the Records menu, point to Filter, and then click Advanced Filter/Sort.</li> In the Field list, select CustomerID.</li>  In the Criteria box under CustomerID, paste the following code: Like &quot;A*&quot; </li> On the Filter menu, click Apply Filter/Sort.</li> On the File menu, click Save.</li> On the File menu, click Close.</li> In the Database window, click Queries.</li> Right-click Query1, and then click Export.</li> In the Export Query 'Query1' To dialog box, select XML in the Save as type list, and then click Export.</li> In the Export XML dialog box, click More Options.</li> 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.</li></ol>

Access 2007
<ol> Open the Northwind sample database.</li> On the Create tab, click Query Design, and then click Close.</li> On the Design tab, click SQL View in the Results group.</li> <li> 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. </li> <li>Click the Microsoft Office Button, and then click Save.</li> <li>In the Query Name box of the Save As dialog box, type Query1, and then click OK.</li> <li>On the Home tab, in the Views group, click View, and then click Datasheet View.</li> <li>On the Home tab, click Advanced in the Sort & Filter group, and then click Advanced Filter/Sort.</li> <li>In the Field list, select First Name</li> <li> In the Criteria box under First Name, paste the following code: Like &quot;A*&quot; </li> <li>On the Home tab, click Advanced in the Sort & Filter group, and then click Apply Filter/Sort.</li> <li>Click the Microsoft Office Button, and then click Save.</li> <li>Right-click Query1, point to Export, click XML File, and then click OK.</li> <li>In the Export XML dialog box, click More Options.</li> <li>In the Export XML dialog box, click the Data tab.</li> <li>Under Records To Export, select the Apply existing filter option, and then click OK.</li> <li>In the Export - XML File dialog box, click Close.</li></ol>

<div class="status_section">

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

<div class="moreinformation_section">

Steps to reproduce the problem in Access 2003
<ol> <li>Open the Northwind sample database.</li> <li>On the Insert menu, click Query.</li> <li>In the New Query dialog box, select Design View and then click OK.</li> <li>On the View menu, click SQL View.</li> <li> In the Query window, paste the following query: SELECT Customers.CustomerID FROM Customers WHERE (((Customers.CustomerID) Like &quot;A*&quot;)); </li> <li>Click Save on the File menu.</li> <li>In the Query Name box that is in the Save As dialog box, type Query1 .</li> <li>On the File menu, click Close.</li> <li>Right-click Query1 and then click Export.</li> <li>In the Export Query 'Query1' To... dialog box, select XML in the Save as type list and then click Export.</li> <li>In the Export XML dialog box, click OK to save the XML-related files in the default location.</li> <li>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.</li></ol>

<div class="references_section">