Persist Array to Table

Posted on 2008-11-03
Last Modified: 2013-11-25
I am receiving a multidimensional array from outside my application (via web service).  I need to persist this to a table in a database.  Is there is a simple/efficient way to process this array?  The code below is too slow Example1 as it is linear function O(m*n) see Google result:   Example2 is slightly faster (not as flexible) but it still needs to iterate the maxRow times say 5000.   I am just looking to see if there is a better way to store an array to a table.
'Example 1

    For i = 0 To maxRow

        For j= 0 To maxColumn

            'save to db

        Next j

    Next i

    'Example 2

    For i = 0 to maxRow




    next i

Open in new window

Question by:CyberUnDead
    LVL 84

    Accepted Solution

    Not that I'm aware of ... at some point you must iterate the "rows" of your array, especially if you're not looking to simply dump into a table ... for example, if you must "massage" the data in any way (i.e. make sure a Date exists, or make sure a particular column is not null) then you don't really have a choice.

    How often do you do this? I'd think you could process 5k rows pretty quickly, assuming you persist the array to disc before you work with it.

    Author Comment


      Thanks for the reply.   If I wanted to just simply dump to a database or file how would one accomplish that?  Again my data is an multidimensional array say MyQueryBean(rows,columns).   The class below is the structure holding the data.  The class was generated by Microsoft Office 2k3 Web Services toolkit  
    Option Compare Database
    Option Explicit
    Public columnList As Variant
    Public data As Variant

    Open in new window


    Author Closing Comment

    I am having the Web Service provider present the data in a different format.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…

    737 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

    18 Experts available now in Live!

    Get 1:1 Help Now