import xml into access 2007/2010

Posted on 2011-09-27
Medium Priority
Last Modified: 2012-06-13
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?
Question by:drtopserv
  • 5
  • 3
  • 3

Author Comment

ID: 36707966
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 !:}?

Expert Comment

ID: 36708015
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.
Technology Partners: 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

ID: 36708175
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,.

Expert Comment

ID: 36708202
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.
LVL 58
ID: 36708979
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


Author Comment

ID: 36984485
wow.seems intreseting code, i need first to learn it!!!:}
LVL 58
ID: 36985467

 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.


Author Comment

ID: 37360520
may u plz post me xml file example that attached with mdb to try it, still hard to understand if u maY:}}
LVL 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 1500 total points
ID: 37363731

 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.


Author Closing Comment

ID: 38082010
explination help out.\

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

864 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