SteveL13
asked on
How import XML file into database
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)
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
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"
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
Can you import XML file manually with expected result?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.x ml", acStructureAndData" but don't know how to replace "C:\YourFolder\yourtable.x ml" with the file I located with the navigation code.
--Steve
Scott: I tried "Application.ImportXML "C:\YourFolder\yourtable.x
--Steve
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I suggested you use Application.ImportXML here: https://www.experts-exchange.com/questions/28530997/How-import-XML-file-into-database.html?anchorAnswerId=40361154#a40361154
als315 provided you the code to use the FileDialog: https://www.experts-exchange.com/questions/28530997/How-import-XML-file-into-database.html?anchorAnswerId=40361473#a40361473
So it would seem the points should be split.
I've asked the Moderators to review.
als315 provided you the code to use the FileDialog: https://www.experts-exchange.com/questions/28530997/How-import-XML-file-into-database.html?anchorAnswerId=40361473#a40361473
So it would seem the points should be split.
I've asked the Moderators to review.
ASKER
You are correct. I missed checking your checkbox. They should allow the split.