• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 625
  • Last Modified:

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

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

2 Solutions
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
You could load it into an Excel spreadsheet via vba with .copyfromrecordset method and append the spreadsheet content to a table.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now