• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 978
  • Last Modified:

import xml into access 2007/2010

I`m new in xml world , & need help around.
well i have large xml file (more than 30mb) and need to import it into access table.
the problem is, that when i perform the import i just get many tables without relationship between them:{
when i open the same xml file in excel 2007/2010 i got it as speadsheet (columns and rows) , and that wat i need to have in access ,
how can i perform this task in access vba (automation-import process) and have the tables linked , or have it as 1 table with fields and rows?
  • 5
  • 3
  • 3
1 Solution
drtopservAuthor Commented:
I think u r trying to answer the Q by applying the answer to implement THE SLA of this services website.
i know how to search the web (google) and have code to import the xml into access with table without no connection between them.that`s what i explain clearly in my Q .
u suggestion was to perfom the import normally without having the link between them.
i need to solve my problem mention in the Q. :}
again: How could i import xml into access 2007/2010 through vba and have the table links (with a relationship) exactly like spreadsheet in excel (when i import xml into excel speadsheet) i have them with columns and rows, that what i need to have in access 2007/2010 !:}?
So the solution is, to write vba script which will read and traverse xml file, create data row as you need and than inserts it into Access table by sql query.

How to traverse xml and insert row to Access table using vba, you can find easily on google.
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

drtopservAuthor Commented:
this what i need, but i don`t know what "traverse xml " means?
also i have search the net around there is lots of articles about xsd/xsl/xml schema etcs...
i need simple way or person who works with this field that may help me out (maybe u) and tell me how i can perform this task,.
Traversing xml means to read each (or only some) of nodes in xml, get the values and do with values what you need.

Good example how to get values from xml is in this soluted question from EE.

If you will need more help, send example of data in your xml and I'll try.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I haven't read through the other links, so pardon me if I'm repeating here.

Access expects XML to be "well formed" for import.  If you run the import process and don't like what you get, then your only choice is to parse the file yourself.

  That can be done using the document object like shown in the attached code.  Basically you end up with a tree structure that you walk and grab the value for each of the elements.


Public Function ImportXMLFile(ByVal strFile As String) As Boolean
        ' OCS - JRD - 04/05/08 - Initial write.
        ' This function will process a single "time form" XML file
        ' and makes fundamental assumptions about the format of the XML file.
        ' If the XML file format is changed in any way, this code will fail.
        ' Each file is expected to have the following data:
        '     SmartformInstance        (multiple per file)
        '       Form                   (one set per form instance)
        '         JobInfo              (one set per form instance)
        '         TravelStart          (one set per form instance)
        '         TravelEnd            (one set per form instance)
        '         ClockIn              (one set per form instance)
        '         StartBreak           (one set per form instance)
        '         EndBreak             (one set per form instance)
        '         ClockOut             (one set per form instance)
        ' For the sections (Travel Start through Clock Out), seven (7)
        ' employee fields are expected.
        Dim objDomDoc As New DOMDocument
        Dim objNodeSmartForm As IXMLDOMNode
        Dim objNode_Sections As IXMLDOMNode
        Dim objNodeData As IXMLDOMNode
        Dim objNodeItem As IXMLDOMNode
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim strSQL As String
        Dim lngForm As Long
        Dim intSection As Integer
        Dim intEmployee As Integer
        Dim strInstanceID As String
        Dim strInstanceDate As String
        Dim strInstanceType As String
        Dim strFormName As String
        Dim strJobNumber As String
        Dim strPerDiem As String
        Dim strValue As String
10      On Error GoTo ImportXMLFile_Error
20      ImportXMLFile = False
        ' Open a DAO Database connection
30      Set dbs = CurrentDb()
        ' Verify that such a file exists.  If not, then exit
40      If Dir(strFile) <> "" Then
          ' Open DOM object by loading the xml file.
50        objDomDoc.async = False
60        objDomDoc.Load strFile
          ' Loop through the form nodes.
70        For Each objNodeSmartForm In objDomDoc.documentElement.childNodes
            '  Get the header info
80          strInstanceID = Nz(objNodeSmartForm.childNodes(0).Text, "")
90          strInstanceDate = Nz(objNodeSmartForm.childNodes(1).Text, "")
100         strInstanceType = Nz(objNodeSmartForm.childNodes(2).Text, "")
110         strFormName = Nz(objNodeSmartForm.childNodes(3).childNodes(0).Text, "")
120         strJobNumber = Nz(objNodeSmartForm.childNodes(3).childNodes(2).childNodes(1).childNodes(2).Text, "")
130         strPerDiem = Nz(objNodeSmartForm.childNodes(3).childNodes(2).childNodes(2).childNodes(2).Text, "")
            ' Save header info
140         strSQL = "SELECT * FROM tblSmartFormInstance WHERE 1=0"
150         Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
160         rst.AddNew
170         rst![FormInstanceID] = strInstanceID
180         rst![FormInstanceDate] = CDate(Mid(strInstanceDate, 6, 2) & "/" & Mid(strInstanceDate, 9, 2) & "/" & Mid(strInstanceDate, 1, 4) & " " & Mid(strInstanceDate, 12, 8))
190         rst![TypeCode] = strInstanceType
200         rst![FormName] = strFormName
210         rst![JobNumber] = strJobNumber
220         rst![PerDiem] = strPerDiem
230         rst.Update
240         rst.Close
            ' Now get the details for the form
            ' Open detail table
250         strSQL = "SELECT * FROM tblSmartFormInstanceDetail WHERE 1=0"
260         Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
            ' Process the sections.  The code below depends on the same sections
            ' being there each time, having the same number of elements,
            ' and being in the same order.
            ' A more flexible approach would be to check the element data,
            ' then do a lookup on the section and field names and map to the tables.
            ' However we are doing only one form at the moment and the data will
            ' always be provided in the current format.
270         Set objNode_Sections = objNodeSmartForm.childNodes(3)
280         For intSection = 3 To 8
290           Set objNodeData = objNode_Sections.childNodes(intSection)
              ' objNodeData.childNodes(0).text is the section name
300           For intEmployee = 1 To 7
310             Set objNodeItem = objNodeData.childNodes(intEmployee)
320             If Nz(objNodeItem.childNodes(2).Text, "") <> "" Then
330               strValue = Nz(objNodeItem.childNodes(2).Text, "")
340               If strValue <> "" Then
350                 rst.AddNew
360                 rst![FormInstanceID] = strInstanceID
370                 rst![SectionTypeID] = intSection - 2
380                 rst![EmployeeListOrder] = intEmployee
390                 rst![EmployeeName] = strValue
400                 rst.Update
410               End If
420             End If
430             Set objNodeItem = Nothing
440           Next intEmployee
450           Set objNodeData = Nothing
460         Next intSection
470         rst.Close
480       Next
490       ImportXMLFile = True
500     End If
510     On Error Resume Next
        ' Clean up
520     Set rst = Nothing
530     Set dbs = Nothing
540     Set objNodeItem = Nothing
550     Set objNodeData = Nothing
560     Set objNode_Sections = Nothing
570     Set objNodeSmartForm = Nothing
580     Set objDomDoc = Nothing
590     Exit Function
600     MsgBox "Unexpected Error"
610     Resume ImportXMLFile_Exit
End Function

drtopservAuthor Commented:
wow.seems intreseting code, i need first to learn it!!!:}
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

 It actually is fairly simple.  You delcare a document object and then load the XML file into it.  When that's done, you have a tree structure (think of a tree view control) for all the nodes of the XML file.

  You can then "walk" that structure, picking and choosiing the elements you need and save as appropriate.

  With this method, it doesn't matter what the XML looks like.

drtopservAuthor Commented:
may u plz post me xml file example that attached with mdb to try it, still hard to understand if u maY:}}
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

 It is not so much the code there, but the overall technique that you need to focus on.  

Once you have set a reference to the MS XML lib in Access, you can then work with the Document Object.  The document object has a set of properties and methods that you can use to work with an XML file.

  First you load the XML file into the document object:

60        objDomDoc.Load strFile

and once that is done, can access the various objects within it:

          ' Loop through the form nodes.
70        For Each objNodeSmartForm In objDomDoc.documentElement.childNodes

  This is all covered at:


Look at the menu down the left hand side of that page.  That explains in detail what the Dcoument object is, its properties and methods, and how to use it to work with XML files along with plenty of examples.

drtopservAuthor Commented:
explination help out.\
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now