Bug involving ADO XML persistance format?
Brendan Reynolds
Because of the number of different technologies involved, I've been having
some difficulty determining the most appropriate newsgroup in which to post
this.
Here are full steps to reproduce the problem using Access 2003 and
Northwind. (I would expect the results to be the same using Access 2000 or
2002, but haven't actually tested with those versions).
1) Add a reference to the Microsoft ActiveX Data Objects 2.7 Library. (I've
also reproduced the problem with other versions of ADO.)
2) In a new standard VBA module, add the following code.
Option Compare Database
Option Explicit
Public Sub SaveRecordset()
Dim rst As New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.Open "SELECT CategoryID, CategoryName, Description FROM Categories"
.ActiveConnection = Nothing
.Save CurrentProject.Path & "\Categories.xml", adPersistXML
.Close
End With
End Sub
Public Sub CountRecords()
Dim rst As New ADODB.Recordset
rst.Open CurrentProject.path & "\Categories.xml"
Debug.Print rst.RecordCount
rst.Close
End Sub
3) From the Immediate window, execute the SaveRecordset procedure, then the
CountRecords procedure. In an unmodified Northwind, the result printed to
the Immediate window is 8, which is correct.
4) Open the saved XML file (Categories.xml) in Notepad. Change the line that
reads ...
<z:row CategoryID='1' CategoryName='Beverages' Description='Soft drinks,
coffees, teas, beers, and ales'/>
... so that it reads as follows ...
<z:row CategoryID='1' CategoryName='Beverages' Description='Soft drinks,
coffees, teas, beers, and ales'></z:row>
For anyone who may not be familiar with XML, these two forms are logically
identical and should be seen as such by any XML parser. The first form is
merely an abbreviation of the second form. Internet Explorer 6, for example,
displays the XML file in exactly the same way (using the abbreviated form)
regardless of which form is actually used within the XML file.
6) Save the XML file.
7) Execute the 'CountRecords' procedure again. The result printed to the
Immediate window is now 1 - ADO sees only the first record. I thought at
first that perhaps ADO was seeing the eight records as one, but by counting
the number of fields in the recordset, I determined that in fact ADO was
reading only the first record, and not reading past the first "><z:row>" in
the XML.
Why is this a problem, you may ask - why not just use the syntax that works?
Unfortunately, I don't have that choice. In the real-world application, I'm
not saving the recordset from VBA code, I'm getting it from a .NET web
service, which uses XSLT to transform the XML from the .NET "diffgram" XML
format to the ADO XML persistence format. The result of the XSLT
transformation is an XML file using the non-abbreviated form, and I'm told
by people in the XSL newsgroup that I can't change that.
The code used in the web service was adapted from KB article 303016, "HOW
TO: Use a DataSet with the Office XP Chart Component and ASP.NET". So, as an
experiment, I went back and followed through that KB article again, this
time not adapting the code but copying and pasting it, changing only the few
things that I had to change to get it to run on my system, such as the SQL
Server connection string and the version of the Office Chart Component (11
on my system, 10 in the KB article). The result exhibits the same problem -
the chart shows a single column, the column corresponding to the first
record in the XML file, the remaining records are ignored.
I'm currently working around the problem by using Replace() in the VBA code
to replace all instances of "><z:row>" in the string returned by the web
service with "/>", and this works, but is obviously not very elegant or
efficient. I'd like to know a) is this a known issue? and b) is there a
better solution or workaround?
--
Brendan Reynolds (Access MVP)
http://brenreyn.blogspot.com
The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
Brendan Reynolds
"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
news:uDTXEdpX...@TK2MSFTNGP09.phx.gbl...
<snip>
> I'm currently working around the problem by using Replace() in the VBA
code
> to replace all instances of "><z:row>" in the string returned by the web
> service with "/>"
I meant, of course, "></z:row>", with the forward slash.
--
Brendan Reynolds (MVP)