?
Solved

How import XML file into database

Posted on 2014-10-03
7
Medium Priority
?
339 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
6 Comments
 
LVL 41

Expert Comment

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

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 41

Assisted Solution

by:als315
als315 earned 1000 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 85
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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Implementing simple internal controls in the Microsoft Access application.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

593 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