[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to import an XML file into MS Access 2007

Posted on 2012-09-09
6
Medium Priority
?
917 Views
Last Modified: 2012-09-28
I am trying to import an xml file  into MS Access

When I manually import the file using Structure and Data it imports fine, but creates several tables within MS Access: Cell, DocProperties, ExcelWorkbook,Pane,Style,Table, and Worksheet Options.  I find all of my data in the table called Cell and with a column called Data.

When I try to import the file into MS Access using the below code it only works if the 7 tables above still exist.  Am I always forced to load this file into a table called Cell ?  Why do I need to maintain all of these 7 tables in order to do the import from the XML file into MS Access ?  

Is there a better way of importing an XML file int MS Access than I have provided ?  

Const acAppendData = 2
Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\Scripts\Test.mdb"
objAccess.ImportXML "c:\scripts\test.xml", acAppendData

Open in new window

0
Comment
Question by:upobDaPlaya
  • 3
  • 2
6 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1600 total points
ID: 38382452
ImportXML is fine for many things, but if you need tight control over exactly what happens with the XML file you'll need to use the XML libraries to move the data into Access.

There's a sample bit of coding in this thread:

http://www.pcreview.co.uk/forums/parse-xml-vba-t2925723.html

Basically you open the XML file and "walk" the elements, and figure out what to do with each one (i.e. import into Table1, for example, or update an existing record in Table2).

Try the sample above, and post back here with questions.
0
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 400 total points
ID: 38383120
Part of the reason the tables have to exist is that you are specifying acAppendData to the ImportXML method.

Can you post a reasonably small sample of the XML? (<50k)

One possibility is to create an XSLT that produces a version of the XML with only the elements you need.  This is similar to the LSMConsulting suggestion, differing in the location of the code.
0
 

Author Comment

by:upobDaPlaya
ID: 38393603
Thanks for the responses..I am out of town for a few days so on Sunday I will give this a try and post back...I want some time to really review the code and make sure I understand..I apologize in advance for not be able to close out the questions sooner as protocol may dictate..Many thanks again for your feedback...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:upobDaPlaya
ID: 38427368
I am working with the sample code provided by LMS and I think this will work well..I debated about programatically opening the file in excel and saving it as an xlsx file, but the code provided is more straight-thru...

Although one thing I can not figure out is what .nextnode is doing in the code ?
0
 
LVL 85
ID: 38428931
.NextNode "gets" the next node in the XML file. It's one of several ways you can "walk" the XML file.
0
 

Author Closing Comment

by:upobDaPlaya
ID: 38446487
Very cool..thanks
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question