Re: Export Query To XML

I am not particularly knowledgeable, but with a little cheating using two
knowledge base articles, I did it. - Mike Gramelspacher

'------------------------------
Sub ExportXMLFromADO()
'------------------------------
' l. Open SQL in ADO Recordset
' 2. Create new DOM
' 3. Save recordset to DOM
' 4. create another new DOM
' 5. Load .xsl (from KB article) into DOM
' 6. Transform DOM from attribute-centric to element-centric DOM using
..xsl
' 7. Save DOM as XML file

' requires Microsoft ActiveX Data Objects 2.5 Library of higher
' to persist XML directly to a DOM. See: Persisting XML Directly into
' DOM Causes Run-Time Error '438', kb264869

Dim szConnect As String
Dim SQL As String

szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\" & _
"Documents and Settings\Mike Gramelspacher\My Documents\My
Database\AnzeigerTest2K.mdb"
SQL = "TestSearchFor"

Dim oRS As ADODB.Recordset
Dim oCN As ADODB.Connection

Set oCN = New ADODB.Connection
Set oRS = New ADODB.Recordset

With oCN
.CursorLocation = adUseClient
.ConnectionString = szConnect
.ConnectionTimeout = 5
.Open szConnect
End With

oRS.Open SQL, oCN

Dim xmlDoc As DOMDocument
Set xmlDoc = New DOMDocument

' To specify a specific version, use a declaration like the following, with
the appropriate version in the ProgID:
' Dim xmlDoc As MSXML2.DOMDocument40
' Set xmlDoc = New MSXML2.DOMDocument40

xmlDoc.async = False
oRS.Save xmlDoc, adPersistXML

If xmlDoc.parseError.errorCode <> 0 Then
MsgBox "Errors During Load" & vbCrLf & xmlDoc.parseError.errorCode &
xmlDoc.parseError.reason
Else
'MsgBox xmlDoc.XML
' See: You cannot import attribute-centric XML in Access, KB285329
' Microsoft Access supports only element-centric XML. XML that is
' persisted from ADO recordsets is created in attribute-centric XML.
' File ADOXMLToAccess.xsl is from this article. It transforms
' attribute-centris XML to element-centric XML.

Dim DOMOut As DOMDocument
Dim domStyle*** As DOMDocument

Set domStyle*** = New DOMDocument
domStyle***.Load "C:\Documents and Settings\Mike Gramelspacher\My
Documents\My Database\Access2KFiles\ADOXMLToAccess.xsl"

'Apply the transform
If Not domStyle*** Is Nothing Then
Set DOMOut = New DOMDocument
xmlDoc.transformNodeToObject domStyle***, DOMOut

'Save the output
DOMOut.Save "C:\Documents and Settings\Mike Gramelspacher\My
Documents\Anzeiger.xml"

'Import the saved document into Access
Application.ImportXML "C:\Documents and Settings\Mike
Gramelspacher\My Documents\Anzeiger.xml"

End If
End If

oRS.Close
oCN.Close
Set oRS = Nothing
Set oCN = Nothing
End Sub

"PC Data***" <nospam@xxxxxxxxxxx> wrote in message
news:HnuMe.6280$RZ2.3501@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> Can Access programatically export a query to XML?
>
> Thanks,
>
> Steve
>


.