Persist Array to Table

Posted on 2008-11-03
Medium Priority
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: http://pages.cs.wisc.edu/~vernon/cs367/notes/3.COMPLEXITY.html.   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
  • 2
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 22875732
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

ID: 22876247

  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 http://msdn.microsoft.com/en-us/library/aa192537(office.11).aspx.  
Option Compare Database
Option Explicit
Public columnList As Variant
Public data As Variant

Open in new window


Author Closing Comment

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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

864 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