Solved

How import XML file into database

Posted on 2014-10-03
7
270 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 39

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

 
LVL 39

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

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…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

803 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