By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,839 Members | 2,270 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,839 IT Pros & Developers. It's quick & easy.

How to convert xml to an access database

P: n/a
Hi

I am fairly new to VBA and want to know how I can convert or I mean
parse the XML file and convert to an access database.

Please help. I greatly appreciate your time and help.
I would be glad to have ur answers ASAP.

Thanks a lot

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Add a reference to Microsoft XML v 3.0 (MSXML3.DLL) to your project.
This will give you access to the XML Document object model (DOM). Load
the XML Document and loop through the nodes in it. Write a record for
each node as appropriate. A sample (unchecked and without error
trapping) is below.

Bill E.
Hollywood, FL

'--------xml document-------------
<?xml version="1.0" encoding="utf-8"?>
<newsgroup name="rec.kites" lastnr="146104">
<article>
<nr>142980</nr>

<aid>&lt;rN***********************@news-text.cableinet.net&gt;</aid>
<references></references>
<from>"Bandulu" &lt;ba*****@REMOVETHISdarkdigital.org&gt;</from>
<date>4/19/2003 10:45:43 AM</date>
<subject>Re: Line Laundry in UK</subject>
</article>
<article>
<nr>142977</nr>
<aid>&lt;b7************@ID-17525.news.dfncis.de&gt;</aid>

<references>&lt;MP************************@news.ci s.dfn.de&gt;</references>
<from>"David Brown :o\)" &lt;ne**@parkcourt.co.uk&gt;</from>
<date>4/18/2003 11:49:29 PM</date>
<subject>Re: At last, a decent pic!</subject>
</article>
</newsgroup>
'----------------------------------
Sub ReadXMLDocument()
Dim strPath as String
Dim oXMLDocument As New MSXML2.DOMDocument
Dim oDocElement As MSXML2.IXMLDOMElement
Dim oNode As IXMLDOMNode

strPath="Enter the path to the Xml Document"

oXMLDocument.Load strPath
Set oDocElement = oXMLDocument.documentElement

'Loop through the top level nodes in the XML document
'Add a record for each one
For Each oNode In oDocElement.childNodes
AddArticle oNode.childNodes(0).Text, oNode.childNodes(1).Text,
oNode.childNodes(2).Text, oNode.childNodes(3).Text,
oNode.childNodes(4).Text, oNode.childNodes(5).Text
Next

Set oNode = Nothing
Set oDocElement = Nothing
Set oXMLDocument = Nothing
End Sub

Function AddArticle(strNr As String, strAID As String, strRef As
String, strFrom As String, strDate As String, strSubject As String)
'Add a record to the articles table tblArticles
Dim strSQL As String

strSQL="INSERT INTO tblArticles (Nr, Aid, References, From, Date,
Subject) "
strSQL=strSQL & "VALUES ('" & strNr & "', '" & strAID & "', '" &
strRef & "', '" & strFrom & "', #" & strDate & "#, '" & strSubject &
"')"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
End Function

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.