Solved

How import XML file into database

Posted on 2014-10-03
7
284 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

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

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

710 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