Solved

Import XML file into access table using VBA

Posted on 2013-12-03
3
10,725 Views
Last Modified: 2013-12-04
Hi Experts,

I am working on an access form that browses for an xml file and then imports it into a table.  I have the browse button complete using VBA, but i am not sure how to write the code to import the XML file into the table.  I would like to import this xml file into a table called "data", and have it create fields for the fields on the XML file.  Here's my code for the browse button if that is any help.  Im not sure how to go about adding the xml file to the table using vba.


Option Compare Database

Option Explicit

Public Function GetFile() As Variant
Dim dialog As Object
Dim pickedfile As Boolean
Set dialog = Application.FileDialog(3)
GetFile = Null
With dialog
    .AllowMultiSelect = False
    .Title = "Please select file for import"
    .Filters.Clear
    .Filters.Add "XML Files", "*.XML"
    pickedfile = False
    pickedfile = .Show
    If pickedfile Then
        GetFile = .SelectedItems.Item(1)
    End If
End With
End Function

Open in new window


Thanks
0
Comment
Question by:Lobb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39694090
Access has the ImportML function:

http://msdn.microsoft.com/en-us/library/office/bb237973%28v=office.12%29.aspx

Essentially you use it like this:

Application.ImportXML "FullPathToYourXMLFile"

In your code above, you'd do this after you've got the file, so:

Dim sFile As String
sFile = GetFile()
Application.ImportXML sFile

You'd then move it over to the table you want.

Here's a TechNet article that might give more insight: http://technet.microsoft.com/en-us/library/ee692914.aspx

That said - generally the builtin method is not full-featured enough to do much, and you often need to do this using the DOM library:

Dim oDoc As MSXML.DOMDocumnet
Set oDoc = New MSXML.DOMDocument

If oDoc.Load("Path to your xml file") Then
  '/ Document is loaded
  Dim oNodes As MXSML.IXMLDOMNodeList
  oNodes = oDoc.Nodes
 
  Dim oNode As MSXML.IXMLDOMNode
  For Each oNode in oNodes
   '/ do something here.
  Next oNode
End If

See this for more information: http://msdn.microsoft.com/en-us/library/aa468547.aspx
0
 

Author Comment

by:Lobb
ID: 39696533
Thanks for your help so far LSMconsulting,

I've created a button on the form to submit this data. I am not sure how to put this file into a specific table.   Could you help me out on importing the data to a table? So far here is the code I have.  Also, it the import options line a built in option or do I need to specify it?

Private Sub Command10_Click()

Dim File As Variant
File = GetFile()
If IsNull(File) Then
    MsgBox "Nothing was selected", vbOKOnly
Else
    Me.FName = File


Application.ImportXML _
    DataSource:=Me.FName, _
    ImportOptions:=acStructureAndData
 
End If
End Sub

Open in new window

0
 

Author Comment

by:Lobb
ID: 39696569
I figured out how to do the import options..  I didn't realize there was a button to make your own options. Thanks for your help LSMconsulting!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

688 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