Solved

How import XML file into database

Posted on 2014-10-03
7
273 Views
Last Modified: 2014-10-08
I want to import an XML file into an Access database.  I have already exported the file and have got two files on my desktop which is where I exported them to.  They are named "LOCALtblTEMPIHMGNotes.xsd" and "LOCALtblTEMPIHMGNotes.xml".

I currently browse for the import file via: (which works fine for an Excel file import but I want to change to XML)

   
 Dim db As Database
    Dim db2 As DAO.Database
    Dim fd As FileDialog, SelectFolderOrFile
    Dim rstMemberRunningNotes As DAO.Recordset
    Dim rstTempFieldNotesImport As DAO.Recordset
    Dim strIHMGNotesID As String
    Dim strMemberID As String
    Dim strRunningNote As String
    Dim strSQL As String
'    Dim NewTesttxtFile As String

    Set db = CurrentDb
    Set db2 = CurrentDb
        
        If MsgBox("This function will update the selected record in the IHMG Notes table. Do you want to continue?", vbYesNo + vbQuestion + vbDefaultButton2) = vbYes Then
        
        'This query deletes any existing records in tblTempFieldNotesImport
        db.Execute "delqryDeleteRecordFromtblTempFieldNotesImport"
        
        'This query deletes any existing records in tblTEMPORARY
        db.Execute "delqryDeleteRecordFromTEMPORARYtbl"
        
       'This code imports the selected Excel file into tblTempFieldNotesImport if the file name is different every time
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
        With fd
            .InitialFileName = Environ("userprofile") & "\Documents\"
            If .Show Then
            'This line of code is for an Excel formatted file...
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTempFieldNotesImport", .SelectedItems(1), False, "A1:B66"

Open in new window


I do not have a clue as to how to change the code to allow me to browse for the XML file and proceed with the import of it into "tblTempFieldNotesImport"

--Steve
0
Comment
Question by:SteveL13
  • 2
  • 2
  • 2
7 Comments
 
LVL 40

Expert Comment

by:als315
ID: 40360956
Can you import XML file manually with expected result?
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 40361154
If your XML is structured correctly for Access, you can use the ImportXML method:

ImportXML "Path to your file", acStructureAndData

However, Access is quite picky about structure, so be aware of that. You'll also need the .xsd file that goes along with the xml file so that Access can correctly create the table structure (assuming you're creating a new table, of course).

IMO, there are too many "ifs" with that method, and you're much better off using the DOM libraries to do this. It's tricky, but it's the most reliable way to move XML into Access.

Here's a good starting point for that: http://msdn.microsoft.com/en-us/library/aa163921%28v=office.10%29.aspx.
0
 

Author Comment

by:SteveL13
ID: 40361193
als315:   Yes, I can import manually with expected result.  The challenge is to write the VBA code to allow me to navigate for the file (the XML file even though there is also a XSD file there), and do the import "automatically".  They will have to be navigated to because in reality they will have a person specific name such as "LOCALtblTEMPIHMGNotes - firstnamelastname.xsd" and "LOCALtblTEMPIHMGNotes - fistnamelastname.xml".

Scott:  I tried "Application.ImportXML "C:\YourFolder\yourtable.xml", acStructureAndData" but don't know how to replace "C:\YourFolder\yourtable.xml" with the file I located with the navigation code.

--Steve
0
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.

 
LVL 40

Assisted Solution

by:als315
als315 earned 250 total points
ID: 40361473
You can try this code:
Dim fd As Object
Set fd = Application.FileDialog(3)
With fd
    .InitialFileName = Environ("userprofile") & "\Documents\"
    .Filters.Clear
    .Filters.Add "XML Files", "*.XML"
    If .Show Then
        Application.ImportXML .SelectedItems(1), acStructureAndData
    End If
End With

Open in new window

0
 
LVL 84
ID: 40363390
I suggested you use Application.ImportXML here: http://www.experts-exchange.com/Database/MS_Access/Q_28530997.html#a40361154

als315 provided you the code to use the FileDialog: http://www.experts-exchange.com/Database/MS_Access/Q_28530997.html#a40361473

So it would seem the points should be split.

I've asked the Moderators to review.
0
 

Author Comment

by:SteveL13
ID: 40363517
You are correct.  I missed checking your checkbox.  They should allow the split.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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.
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…

860 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