Re: Export Query To XML

Thanks, Mike, appreciate the help!


"Mike Gramelspacher" <gramelsp@xxxxxxxx> wrote in message
news:ddu29g$dh8$1@xxxxxxxxxxxxxxxxxxxx
>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
>>
>
>


.