Load object (array like) into table in one go.

Posted on 2010-01-06
Last Modified: 2013-11-27
I have an object (cellset) that contains multiple dimensions much like an array.
Currently, I'm looping through the cellset adding each record to a table 1 row at a time.
Is there a way to load the contents of the object in one go.  I remeber using this trick in Excel once to load an array to a worksheet in 1 go.
The reason for this is that the cellset is 30,000 rows and it's currently taking 4 minutes to load the table.  Given the cellset is in memory, it should be much quicker than this.
See the code below I'm using to load the table one row at a time.
mCellset.Open sQry, cnn


    'Tidy the query of an erroneous spaces

    sQry = Trim(sQry)


    'Open the query that was constructed above

    With mCellset

        'open access recordset to write cube query to

        mConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AppGlobals.DBLOCATION

        Set recSRC = New ADODB.Recordset


        recSRC.Open "Test", mConnection, adOpenKeyset, adLockOptimistic, adCmdTableDirect


        For j = 0 To mCellset.Axes(1).Positions.Count - 1


            sValue = mCellset(0, j)

            dim1 = mCellset.Axes(1).Positions(j).Members(0).Caption

            dim2 = mCellset.Axes(1).Positions(j).Members(1).Caption

            dim3 = mCellset.Axes(1).Positions(j).Members(2).Caption



            With recSRC


                !Dimension1 = dim1

                !Dimension2 = dim2

                !Dimension3 = dim3

                !MeasureValue = sValue


            End With


        Next j

'close recordset



'close cellset


Open in new window

Question by:lee_jd
    LVL 84

    Accepted Solution

    No, you cannot pass an array to a table and have it load all the "rows". The only method is the one you're using - loop through each member of that array and add it. Access doesn't support anything else.
    LVL 18

    Assisted Solution

    You could load it into an Excel spreadsheet via vba with .copyfromrecordset method and append the spreadsheet content to a table.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Suggested Solutions

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    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 Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    794 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

    16 Experts available now in Live!

    Get 1:1 Help Now