import xml into access 2007/2010

Posted on 2011-09-27
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

Expert Comment

ID: 36707920

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.
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.


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 57
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 57
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 57

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

828 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