Solved

How import XML file into database

Posted on 2014-10-03
7
259 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 39

Assisted Solution

by:als315
als315 earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
You are correct.  I missed checking your checkbox.  They should allow the split.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now