[AccessD] ExportXML fails calulated where in acc2003?

Eric Goetz EricGoetz at egisystems.com
Sat Nov 27 20:04:52 CST 2004


Hi,

I thought I'd incorporate a little XML in transferring data from an
Access 2003 mdb to an Excel spreadsheet. The Excel XML List looks like a
useful feature. The trouble is, I can't get Access to output the XML.

I'm running Access 2003 as part of a complete install of Office 2003
Enterprise Edition with SP1. I've tried this on two machines: XPProSP1a
and XPProSP2.

I've wasted my day isolating a test case, and here it is...

Test A, make sure you can do a simple export to XML.
1. Take a fresh copy of Northwind.mdb
2. Right click on the query called "Alphabetical List of Products" and
select "Export..."
3. Use the combobox "Save as type:" to select XML.
4. Click "Export"
5. Take the defaults on the subsequent dialogs.
6. Open the ".xml" file that you just created.
7. WOW! You now know how to use XML!

Test B, actually try to use XML to do some work.
1. Open the query "Alphabetical List of Products" in design view.
2. Add criteria to "CatagoryName" of Like "S*", which gives SQL of:
      SELECT Products.*, Categories.CategoryName
      FROM Categories INNER JOIN Products ON 
      Categories.CategoryID = Products.CategoryID
      WHERE (((Categories.CategoryName) Like "S*") AND       
             (Products.Discontinued)=No));
3. Run the query to see that you get the 12 products from the Seafood
category.
4. Save the query.
5. Perform steps 2 through 6 from Test A.
6. I get a nearly empty file. HUH? I thought I knew how to use XML.

How about you?

It seems that just about any calculation in the WHERE clause kills
ExportXML. I've tried it from VBA as well as the manual method employed
above.

Thanks,
 
Eric
 




More information about the AccessD mailing list