• 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
                .AddNew
                !Dimension1 = dim1
                !Dimension2 = dim2
                !Dimension3 = dim3
                !MeasureValue = sValue
                .Update
            End With
       

        Next j


'close recordset
recSRC.Close
 
'close cellset
mCellset.Close

Open in new window

0
lee_jd
Asked:
lee_jd
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.
0
 
SimonCommented:
You could load it into an Excel spreadsheet via vba with .copyfromrecordset method and append the spreadsheet content to a table.
0

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