Importing XML Data in Access 2003



I have an XML file with first & Lastname and other pertinent info that I
would like to import into a blank Access db, but the data imports as several
tables grouped by the column headings. So when you finish importing, one
record takes up several rows, instead of one. How can I import this xml data
so that everything pertaining to a person is imported into one record and
only takes up one row? I've also tried to import the xml data file into
Excel and I get the same problem.


Don't know how your XML is formatted but I think you should be able to use
something like:

Dim InPut, FoundStr as string
Dim StrArr
Set db = CurrentDb
Set rec = db.OpenRecordset("table", dbOpenDynaset)
InPut = "filename.xml"

Open InPut For input As #1
Do While Not EOF(1)
Line Input #1, FoundStr
... 'Use code to split line into array elements
... 'Use code to format individual array elements
' Now write the cleaned elements into a table
rec("FirstName") = StrArr(<element>)
rec("LastName") = StrArr(<element>)


Close #1
Set db = Nothing
Set rec = Nothing

Good luck!

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question