Re: How to Programmatically Export as XML & SXL

From: Graham R Seach (gseach_at_NOSPAMpacificdb.com.au)
Date: 06/06/04 

Date: Mon, 7 Jun 2004 09:30:42 +1000

Permood,

Unfortunately, I've had precious little to do with XML in Access, so I'm
afraid I'm not going to be much help to you in the short term. I will
certainly read up on the topic, but that's not going to help you right now.
All I can offer at the moment, is what Luiz suggests, the
Application.ExportXML method. There is also an Application.TransformXML
method. The online Help provides a little information, but that may be
enough to get you started.

Sorry I can't be of more help right now.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

"Permood" <ExpertPro@Hotmail.com> wrote in message
news:178a301c44995$6d147af0$a601280a@phx.gbl...
> Hi Graham R Seach,
> Thank you for such a detail answer. (I bought the book,
> Its really nice book)
>
> I am very new to XML, HTML stuff. My client want me to
> Export data in XML format, plus they want me to create
> HTML file base on this XML export using CSS files. Sort of
> Microsoft help file html. Because in the memo field they
> have hyperlinks Ref., like any office/access help file. I
> really don't know how could i use CSS file to create that
> html, XML based output within Access.
> IF you can kindly explain me bit more about this, or tell
> me about any book/Add-in which can perform this task.
>
> Thanks a lot for your help.
>
> Permood
>
> >-----Original Message-----
> >Permood,
> >
> >To do this, you use the ADO Recordset object's Save
> method. The following
> >examples demonstrate how to save, re-open, modify, then
> re-save such a
> >recordset. Not all providers allow you to save a
> recordset to a file. You're
> >safe with the Jet OLE DB provider, but to be certain with
> other providers,
> >open the recordset using a client-side cursor.
> >
> >Dim rs As ADODB.Recordset
> >Dim strADTGFile As String
> >Dim strXMLFile As String
> >
> >Set rs = New ADODB.Recordset
> >
> >'Open the recordset
> >rs.CursorLocation = adUseClient
> >rs.Open "Customers", CurrentProject.Connection, _
> >adOpenStatic, adLockOptimistic, adCmdTable
> >
> >'Specify the output files
> >strADTGFile = "c:\Temp\Customers.adtg"
> >strXMLFile = "c:\Temp\Customers.xml"
> >
> >'You'll get a runtime error if you try to save a
> recordset to a file that
> >'already exists, so we have to delete any existing file
> first. But if you
> >'try to delete a file that doesn't exist, you'll still
> get a runtime error.
> >
> >On Error Resume Next
> >Kill strADTGFile
> >Kill strXMLFile
> >Err.Clear
> >On Error GoTo 0
> >
> >'Now use the Save method to save the recordset to disk.
> You have two
> >'options with regard to file formats; Advanced Data
> Tablegram (ADTG),
> >'which is a proprietary Microsoft format, or XML format.
> Saving the
> >'recordset in XML format is great if you intend to
> exchange data with
> >'another application that supports XML, but the ADTG
> format will produce
> >'a smaller file size. Save the recordset to disk as an
> ADT file
> >rs.Save strADTGFile, adPersistADTG
> >
> >'Just to show that it can be done, save
> >'the recordset to disk as an XML file
> >rs.Save strXMLFile, adPersistXML
> >
> >'Clean up
> >rs.Close
> >Set rs = Nothing
> >Set cn = Nothing
> >
> >'Leave both files on the disk for the present, because we
> haven't finished
> >'with them yet. If we were to continue working with the
> recordset, adding
> >'and deleting rows, or modifying data, the changes would
> be reflected in
> >'the database; not the file. Any changes you want
> reflected in the file
> >'must be explicitly saved to the file - remember, this
> recordset is bound
> >'to the database by a connection!
> >'
> >'The next example shows you how to re-open the recordset
> we saved to
> >'disk in the preceding section, make a change to it, then
> re-save it.
> >
> >Dim rs As ADODB.Recordset
> >Dim strADTGFile As String
> >
> >Set rs = New ADODB.Recordset
> >
> >'Specify the output file
> >strADTGFile = "c:\Temp\Customers.adtg"
> >
> >'When you want to open a recordset using a file as its
> source, you must do
> >'so without specifying a connection. This creates what is
> called, a
> >disconnected
> >'recordset. Once the recordset is open, you can work with
> it just like any
> >other
> >'recordset, but the recordset will be bound to the file -
> not the database!
> >If you
> >'want to bind the recordset to the database, you must
> then set the recordset
> >'s
> >'ActiveConnection property.
> >'
> >'Our example reconnects to the database, but also re-
> saves the recordset to
> >the
> >'file. Open the recordset with a client-side cursor, but
> NO connection!
> >rs.CursorLocation = adUseClient
> >rs.Open strADTGFile, , adOpenStatic, adLockOptimistic
> >
> >'Now set the recordset's connection
> >rs.ActiveConnection = CurrentProject.Connection
> >
> >'Make a change and save it again
> >rs!Fax = "555-1234"
> >rs.Update
> >
> >Kill strADTGFile
> >rs.Save strADTGFile, adPersistADTG
> >
> >'Clean up
> >rs.Close
> >Set rs = Nothing
> >
> >'The final example opens the file again to demonstrate
> that we have indeed
> >'accomplished our goal of saving a modified recordset,
> after which, the two
> >'output files are deleted, since we don't need them any
> more.
> >Dim rs As ADODB.Recordset
> >Dim strADTGFile As String
> >Dim strXMLFile As String
> >
> >Set rs = New ADODB.Recordset
> >
> >'Specify the output file
> >strADTGFile = "c:\Temp\Customers.adtg"
> >
> >'Open the recordset with a client-side cursor,
> >'but NO connection!
> >rs.CursorLocation = adUseClient
> >rs.Open strADTGFile, , adOpenStatic, adLockOptimistic
> >
> >'Now prove that the data had changed since the last
> operation
> >Debug.Print rs!Fax
> >
> >'Clean up
> >rs.Close
> >Set rs = Nothing
> >Kill strADTGFile
> >Kill strXMLFile
> >
> >I hope this helps.
> >
> >Regards,
> >Graham R Seach
> >Microsoft Access MVP
> >Sydney, Australia
> >
> >Microsoft Access 2003 VBA Programmer's Reference
> >http://www.wiley.com/WileyCDA/WileyTitle/productCd-
> 0764559036.html
> >
> >
> >"Permood" <ExpertPro@hotmail.com> wrote in message
> >news:16b7301c44819$f9fdab30$a501280a@phx.gbl...
> >> Hi EveryOne.
> >> I want to export a Query as XML data with SXL. How to do
> >> it using VBA.
> >>
> >> I am using Access 2003.
> >>
> >> Thanking you in advance for your time, and help.
> >>
> >>
> >> Permood
> >
> >
> >.
> >

Relevant Pages

  • Re: Copy Recordset
    ... Dim xml As MSXML2.DOMDocument, ... If what you need are two copies of the same recordset that are independant ... Set rst = New ADODB.Recordset ...
    (microsoft.public.vb.database.ado)
  • Re: For XML -> ADO Recordset
    ... The xml you are getting is not a persisted recordset it is simply pure xml ... Dim oCmd As Command ... Dim rs As New ADODB.Recordset ...
    (microsoft.public.sqlserver.xml)
  • Re: MDAC Converting Dataset to XML
    ... I hadn't realised there was a later version of MDAC than the one we had ... recordset is fine, the code that does this is a mirror of the conversion to ... XML and for completeness I include a copy (albeit ... Can you reproduce the symptom by saving the first piece of xml to a file, ...
    (microsoft.public.data.ado)
  • Re: How to Programmatically Export as XML & SXL
    ... I am very new to XML, ... you use the ADO Recordset object's Save ... >Dim rs As ADODB.Recordset ... >Dim strADTGFile As String ...
    (microsoft.public.access.externaldata)
  • Re: Database + dll
    ... You can include an XML or ADTG persisted Recordset with the ... XML can be compiled into a DLL as a resource, ... copied to a stream first anyway (not that the recordset would alter the ... It's a simple matter of creating a custom resource type from an XML file at ...
    (microsoft.public.vb.database.ado)