Importing XML data into Access 2000

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am developing a database in Access 2000 but have data in .XML format which
I need to import into Access to form the basis of a table for a dropdown menu.

It would appear that there is no direct path of Importing .XML formatted
data into Access 2000, I am aware that Access Xp is able to do it via a menu
command but am not in a position to purchase Office Xp just for this purpose!

Hence does anyone know of a method of importing the data into a form Access
can use (ideally without long and complicated VB scripts as my VB skills
leave a lot to be desired!)

Thanks
 
The only built-in support for XML in Access 2000 is the ability to save and
open an ADO recordset to/from XML. In order to open a recordset from XML,
though, the XML must be in a specific format. If it isn't already in that
format, you need to use XSLT to transform it. The code to do that is not
trivial, and tends to be quite specific to the XML schema you're
transforming from - my code probably wouldn't help you much, because the
schema would be quite different. (Much of it is also in C# rather than VBA).
If you want to pursue that route, start with Microsoft KB article 263247,
"HOWTO: Obtain an ADO Recordset from XML".

Your best bet is probably to ask around in the XML and XSL newsgroups -
someone may be able to advise you on how to transform your XML into a format
that Access can import directly, such as CSV. I tried a web search on the
phrase "transform xml to csv" and came up with a number of hits. There's a
link to one of them below. This is not exactly trivial either, but it is may
be a bit simpler than the ADO recordset route.

http://www.scit.wlv.ac.uk/~jphb/xml/xmlcsv.html
 
I agree that using XML in older versions of Access is not for the faint of
heart.
I spent a weekend writing an XML import routine (for my specific schema) in
A97.
I used the DOM to read the document and then I stepped through it.
It was a real hack - but it worked.

If you can't get a newer version of Acces then just request the file be in a
tabular format, not XML.
e.g. ASCII or Excel or Fox, dBase, Access.
 
Back
Top