Importing XML data into Access and exporting Access table to XML form

  • Thread starter Thread starter Karen Middleton
  • Start date Start date
K

Karen Middleton

Can somebody kindly share the VB Script code which enable me from
command line to export a Access table to XML and similarly to load a
XML file to be loaded into a Access table from command line of a VB
Script.

Thanks
Karen
 
Hi Karen,

This is a whole different bag of worms from writing VBScript to run Jet
queries.

If you have Access 2003, investigate the Application.ImportXML method,
which can import data from some but by no means all XML files. There is
also Application.ExportXML, which can export a table or query to an XML
file. XML support in Access 2002 is feebler, and in earlier versions
non-existent.

The general idea would be something like this

Dim appA 'Access Application

Set appA = GetObject("D:\folder\database.mdb")
appA.ImportXML "D:\folder\filename.xml", 1
'1 = acStructureAndData
'0 = acStructureOnly
'2 = acAppendData
appA.Close

If you don't have Access 2003, the best approach might be to have your
script transform the XML file into a CSV file, and then put that into
the database. Google for "convert XML to CSV" to find conversion
programs.
 
In addition to John's suggestions, an alternative that may be worth
investigating is that ADO can save a recordset as an XML file, or open a
recordset from an XML file or ADO Stream object. You're likely to have to
use XSLT to transform between the ADO schema and the schema of whatever
application you're sharing data with, though.

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

'After running this procedure once, you'll need to uncomment the
following commented
'lines, or manually delete the Categories.xml file, if you want to
run it again.
'On Error Resume Next
'Kill CurrentProject.Path & "\Categories.xml"
'On Error GoTo 0
.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

--
Brendan Reynolds (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.
 
Thanks, Brendan. Shows how often I use ADO!


In addition to John's suggestions, an alternative that may be worth
investigating is that ADO can save a recordset as an XML file, or open a
recordset from an XML file or ADO Stream object. You're likely to have to
use XSLT to transform between the ADO schema and the schema of whatever
application you're sharing data with, though.

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

'After running this procedure once, you'll need to uncomment the
following commented
'lines, or manually delete the Categories.xml file, if you want to
run it again.
'On Error Resume Next
'Kill CurrentProject.Path & "\Categories.xml"
'On Error GoTo 0
.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
Click to expand...
 
This is one of the few things I use it for, and I ended up having to jump
through so many hoops to transform between the XML schema that ADO produced
and the XML schema that the other application required that I'm not at all
sure that I wouldn't have been better of just using old-fashioned I/O
techniques to write the data to a text file, inserting the XML tags as
necessary. There's probably no one correct answer to this, it probably
depends to a large extent on the schema the data needs to be exported
to/imported from, and how similar or otherwise that schema is to the schema
used by ADO or by the new XML features in Access 2003. While XSLT appears
(in my limited experience) to be very powerful, and capable of transforming
between very different schemas, it's not something I would want to use any
more than absolutely necessary.
--
Brendan Reynolds (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.
 
Back
Top