Convert recordset to array

0
votes

I am quite new to access and sql. Trying to convert the data in my recordset into an array (Excel VBA) so that I could work with the data without pasting the data on a spreadsheet.

Did some research and most sites mentioned that GetRows function would work but I got an run-time error '3021'. Any advice on how to fix this, please? Thanks!

Dim conConnect As Object
Dim cmdCommand As Object
Dim rstRecordSet As Object
Dim DBPath, StrCon As String
Dim tblarray As Variant

Set conConnect = CreateObject("ADODB.Connection")
Set cmdCommand = CreateObject("ADODB.Command")
Set rstRecordSet = CreateObject("ADODB.Recordset")
DBPath = "C:\Users\Documents\DbMhours.accdb"

    conConnect.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBPath & _
    ";Mode=Read|Write"

    conConnect.CursorLocation = adUseClient
    conConnect.Open

    With cmdCommand
        .ActiveConnection = conConnect
        .CommandText = "SELECT * FROM tblOverallStats;"
        .CommandType = adCmdText
    End With

    With rstRecordSet
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Open cmdCommand
    End With

    'Call StatsSum function in module
    tblarray = StatsSum (rstRecordSet)

    rstRecordSet.Close

    conConnect.Close


Function StatsSum(Summary As Object) As Variant 'Input to be recordset from workbook

    tblarray = Summary.GetRows

End Function
2
excelvbams-accessrecordset

2 Answers

2
votes

Your function is wrong - it should be:

Function StatsSum(Summary As Object) As Variant 'Input to be recordset from workbook

    StatsSum = Summary.GetRows

End Function

You should also check to see if you have any records first using .EOF

0
votes

After struggling with this issue myself, I realized that the simplest way may simply be brute force:

With rstRecordset
    .MoveLast
    .MoveFirst
    For i = 1 To .RecordCount
        for j = 1 to .Fields.Count
             tblArray(i,j) = .Fields(j).Value
        Next j
        .MoveNext
    Next i
End With