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 domStylesheet As DOMDocument
Set domStylesheet = New DOMDocument
domStylesheet.Load "C:\Documents and Settings\Mike Gramelspacher\My
Documents\My Database\Access2KFiles\ADOXMLToAccess.xsl"
'Apply the transform
If Not domStylesheet Is Nothing Then
Set DOMOut = New DOMDocument
xmlDoc.transformNodeToObject domStylesheet, 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 Datasheet" <no****@nospam.spam> wrote in message
news:Hn*****************@newsread3.news.atl.earthl ink.net...
Can Access programatically export a query to XML?
Thanks,
Steve
