Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    venom is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2021
    Posts
    12

    Importing Data from XML and then Updating Existing Table

    Hi,

    Apologies if anything sound silly in my question below. I am new to access VBA programming and I am trying to parse xml (using MSXML2.DOMDocument60). I am able to loop through nodes / child nodes but when I am trying to update the access table then it takes very long time. If i remove the Update function (RETU_R_Update) then code runs very quickly.
    Can someone please advise what is the best and quickest way to update the existing access table and if there is anything wrong with my code. Please see below my code.

    Variables p_name, pvalue and DN_Name are updated while reading through xml file. XML file is attached too. It is only 19MB in size. Appreciate is someone can help and advise.


    Sub XMLRead()
    DoCmd.SetWarnings False

    Dim path As String
    Dim firstNameField As MSXML2.IXMLDOMNodeList
    Dim lists As MSXML2.IXMLDOMNodeList
    Dim raml As MSXML2.IXMLDOMElement
    Dim RETU_R_Fields As Variant
    Dim RETU_R_Values As Variant


    Dim Fieldname As String

    Dim i As Integer
    path = "C:\Audit_DB\Input Files\Test.xml"



    'Delete all records from Table RETU_R
    DoCmd.RunSQL "Delete * from RETU_R"





    Dim ObjXMLDoc As MSXML2.DOMDocument60
    Set ObjXMLDoc = New MSXML2.DOMDocument60


    ObjXMLDoc.async = False
    ObjXMLDoc.SetProperty "SelectionLanguage", "XPath"
    ObjXMLDoc.SetProperty "ProhibitDTD", False
    ObjXMLDoc.resolveExternals = False
    ObjXMLDoc.validateOnParse = False
    ObjXMLDoc.SetProperty "SelectionNamespaces", "xmlns:r='raml20.xsd'"
    ObjXMLDoc.Load (path)

    If ObjXMLDoc.parseError.errorCode <> 0 Then
    MsgBox "ERROR when loading " + strFileName + ": " + ObjXMLDoc.parseError.reason
    Else
    'MsgBox "Loaded Successfully"
    End If



    Set objDoc = ObjXMLDoc.documentElement


    Dim TitleNodes As MSXML2.IXMLDOMNodeList
    Set TitleNodes = ObjXMLDoc.selectNodes("//r:managedObject")
    Dim NodeElement As MSXML2.IXMLDOMElement
    Dim nodeChild As IXMLDOMElement
    Dim mo As String
    Dim DN_Name As String
    Dim p_name As String
    Dim p_value As String
    Dim l_name As String





    For Each node In TitleNodes
    mo = node.getAttribute("class")
    If mo = "RETU_R" Then
    DN_Name = node.getAttribute("distName")
    For Each par In node.childNodes
    If par.baseName = "p" Then
    p_name = par.getAttribute("name")
    p_value = par.Text
    End If

    If par.baseName = "list" Then
    l_name = par.getAttribute("name")
    For Each list In par.childNodes
    If list.baseName = "item" Then
    For Each itemp In list.childNodes
    p_name = l_name & "_" & itemp.getAttribute("name")
    If itemp.baseName = "p" Then
    p_value = itemp.Text
    End If
    Next
    End If
    Next
    End If
    'Debug.Print p_name, DN_Name, p_value
    Call RETU_R_Update(p_name, p_value, DN_Name)
    Next
    End If
    Next

    MsgBox "XML is Loaded Successfully"
    Debug.Print ObjXMLDoc.selectNodes("//r:managedObject").length
    DoCmd.SetWarnings True
    End Sub


    Code:
    Function RETU_R_Update(p_name As String, p_value As String, DN_Name As String)
        
        'This function will update the records in RETU_R Table
        
        Dim rs As DAO.Recordset
        Dim db As DAO.Database
        Set db = CurrentDb
        Set rs = db.OpenRecordset("RETU_R")
        
        Dim i As Integer
        i = 0
        Dim findDN, myCheck
        Dim SQLString As String
           
        'rs.Open "RETU_R", conn, adOpenKeyset, adLockOptimistic
        
        'Check if DN_Exists
        findDN = DLookup("DN_Name", "RETU_R", "DN_Name = '" & DN_Name & "'")
        
        If myCheck <> IsNull(findDN) Then
            rs.AddNew
            rs.Fields(0).Value = DN_Name
            rs.Fields(1).Value = "RETU_R"
            rs.Fields(22).Value = Now
            rs.Update
            SQLString = "Update RETU_R SET " & p_name & "=""" & p_value & """ where DN_Name" & "=""" & DN_Name & """;"
            DoCmd.RunSQL SQLString
            'rs.Edit
            'rs.Update
            rs.Close
            Exit Function
        Else
            SQLString = "Update RETU_R SET " & p_name & "=""" & p_value & """ where DN_Name" & "=""" & DN_Name & """;"
            DoCmd.RunSQL SQLString
            rs.Edit
            rs.Update
            rs.Close
            Exit Function
        End If
    
    
        rs.Update
        rs.Close
    End Function
    Attached Files Attached Files

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    The line
    If myCheck <> isnull(findDN) is curious. myCheck is a variant and never initialized. findDN is also a variant. isNull(findDN) returns a boolean, probably not what you expect.

    Edit: I had a lot of afterthoughts and have edited this pretty heavily, but here's my final thoughts:

    The DLookup of DN_NAME is time consuming.
    Do you really need to verify that the DN_Name exists before trying the update? The criteria in the update query does that.

    I added OPTION EXPLICIT to the top of the module and found these undeclared variables that I had to add to get a clean compile:

    Dim objDoc As Object
    Dim node As Variant
    Dim par As Variant
    Dim list As Variant
    Dim itemp As Variant

  3. #3
    venom is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2021
    Posts
    12
    Quote Originally Posted by davegri View Post
    The line
    If myCheck <> isnull(findDN) is curious. myCheck is a variant and never initialized. findDN is also a variant. isNull(findDN) returns a boolean, probably not what you expect.

    Edit: I had a lot of afterthoughts and have edited this pretty heavily, but here's my final thoughts:

    The DLookup of DN_NAME is time consuming.
    Do you really need to verify that the DN_Name exists before trying the update? The criteria in the update query does that.

    I added OPTION EXPLICIT to the top of the module and found these undeclared variables that I had to add to get a clean compile:

    Dim objDoc As Object
    Dim node As Variant
    Dim par As Variant
    Dim list As Variant
    Dim itemp As Variant
    Thanks davegri

    Removing DLookup hasn't improved anything, neither adding option explicit has any improvement.

    I removed the myCheck but still doesn't make any difference. It still takes lot of time to update. Apologies, the recommendation is not working for me.

    The code works fine and quick if i don't update the database. The XML file is read very quickly. It is something to do with the function RETU_R_Update.

    I have attached Database with updated code, can you try to run it the XML and see if there is any other efficient way to update Table.

    I understand SQLQuery is run many times for variable, which is possibly not the best approach

    Attached Files Attached Files

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Not sure why you repeat rs.Update and rs.Close
    Can't you establish a connection and hold on to it?, that has to be slowing things down?

    If you walk through your code line by line, perhaps you would see where it takes some time to act on a line of code.?

    Avoid needless repetition is my guess?

    One would also need the xml file, surely?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    With logic in downloaded db from post#3,
    16488 records are created in the table
    262868 calls to the update module are made

    Does that seem right?

  6. #6
    venom is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2021
    Posts
    12
    Hi mate, thanks for the reply.

    I have attached xml in my first post already. I do understand there is needless repetition here but for every record to update, i need to pass it through SQL query. Every singl parameter is parsed from the XML and though it contains over 16000 rows but consider the number of cells that were updated.


    Every Update command is updating single record at a time.

  7. #7
    venom is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2021
    Posts
    12
    Please see that it is parsing a data from number of nodes and child nodes. Once correct value is parsed then it is send to Update function which then checks for the right field (both rows & columns) to update. So considering the iterations it makes horizontally & vertically , it seems right but it is definitely not the best method. Any suggestions are welcomed

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Quote Originally Posted by venom View Post
    Please see that it is parsing a data from number of nodes and child nodes. Once correct value is parsed then it is send to Update function which then checks for the right field (both rows & columns) to update. So considering the iterations it makes horizontally & vertically , it seems right but it is definitely not the best method. Any suggestions are welcomed
    Ah, I see. It inserts a record then runs the update query 15 times for each record inserted so as to update the fields one by one.
    Better would have the update sql built to include ALL the fields horizontally before doing the update once.

  9. #9
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Please have a look at this updated file, I've modified the code to run one update per record and it seems much faster now.

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    venom is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2021
    Posts
    12
    Quote Originally Posted by Gicu View Post
    Please have a look at this updated file, I've modified the code to run one update per record and it seems much faster now.

    Cheers,
    Vlad
    Thanks Gicu, yes it has improved the performance significantly. I will spend sometime now to understand the changes

  11. #11
    venom is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2021
    Posts
    12
    Hi Guys, I appreciate the solution of Gicu has improved the performance but if i read an XML of of 500MB then it will take forever to parse and update multiple tables.

    Any other solution which can improve the time to update Tables?

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    I've none, other than make rs public and open and close once?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  13. #13
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Quote Originally Posted by venom View Post
    Hi Guys, I appreciate the solution of Gicu has improved the performance but if i read an XML of of 500MB then it will take forever to parse and update multiple tables.

    Any other solution which can improve the time to update Tables?
    Any possibility of obtaining your data in some format other than xml?
    Or finding a program to convert xml to maybe csv? Google "XML to CSV converter"

  14. #14
    venom is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2021
    Posts
    12
    Unfortunately not, the table structure is different for every managedObject so i can't export it in one single csv. It has to be in xml and then require parsing to populate in respective tables. The example i showed is of only one Table : RETU_R

  15. #15
    venom is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2021
    Posts
    12
    I tried using entering values through rs.Fields(i) but it is also bit slow. The major prolem using rs.Fields is that it increases the file size significantly and sometimes hits 2GB limit which throws exception. I can compact on close but during update the error is generated as file size increases significantly.

    This doesnt happen with Vlad solution but i still find it slow.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 17
    Last Post: 12-24-2019, 10:35 PM
  2. Replies: 4
    Last Post: 10-09-2017, 03:17 PM
  3. Updating the Existing record with new data
    By rd.prasanna in forum Import/Export Data
    Replies: 4
    Last Post: 10-02-2013, 07:04 AM
  4. Importing Excel data to an existing table
    By tonyrhills in forum Import/Export Data
    Replies: 3
    Last Post: 12-23-2011, 09:19 AM
  5. updating existing report with new data in table
    By newtoaccess123 in forum Reports
    Replies: 2
    Last Post: 10-18-2011, 09:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums