Trying to save the recordset results into array vba

Question

Sub TEST()

Dim str(5)

    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset

If cnn.State = adStateOpen Then cnn.Close
    cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
    ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name
    cnn.Open

If rs.State = adStateOpen Then rs.Close
    rs.CursorLocation = adUseClient

strSQL = "SELECT DISTINCT ProductSubcategoryKey FROM [INSERT$] ORDER BY ProductSubcategoryKey DESC"

    rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

'results are
'45
'40
'30
'34
'32

str(0) = rs(0)
str(1) = rs(1) 'Error occurs here
str(1) = rs(2)
str(2) = rs(3)
str(3) = rs(4)

Debug.Print str(0)
Debug.Print str(1)
Debug.Print str(2)
Debug.Print str(3)
Debug.Print str(4)

End Sub

the above code successfully returns the data into record set but when try to save into array it throws error.. can any one tell what's went wrong ?

Thanks for your time !!

DevelopmentIsMyPassion · Accepted Answer · 2013-12-18T10:19:06.143

1

The error is because you have only selected 1 column. So your recordset includes only 1 column. You have to select 5 columns as per your code.

For your code to work you have to select 5 columns in your query. Otherwise if you just add 1 more column then it will fail on line below str(1) = rs(2)

If you want to print all records from recordset to str array then you have to loop through recordset. Also i would suggest to not have fixed size array. So change in your code from Dim str(5) to Dim str()

Now loop on recordset

For i = 1 to rs.RecordCount
   str(i) = rs(0)
next

edited Dec 18 '13 at 10:19

answered Dec 18 '13 at 10:01

DevelopmentIsMyPassion

3,541
4
34
60

Actually what i'm trying to achieve is ..to get the 5 distinct values from ProductSubcategoryKey column .. can u assist me in this ? – user3114645 Dec 18 '13 at 10:06
bue this wont work as its just 1 column being feteched. do you want to assign those values to str array? – DevelopmentIsMyPassion Dec 18 '13 at 10:07
Then you should loop in record set? do you know how to do it? – DevelopmentIsMyPassion Dec 18 '13 at 10:10
first tell me why you have array for 5 like str(5)? what happens if your query returns more than 5 records? In your case if query returns more than 5 records then you will print only 5 records in the str array – DevelopmentIsMyPassion Dec 18 '13 at 10:13
str(0),str(1),str(2),str(3),str(4) contains = 45 ..i look like str(0)=45,str(1)=40,str(2)=35,str(3)=32,str(4)=30 – user3114645 Dec 18 '13 at 10:52
so you mean you get same values in array str? can you check what you get value of rs(0) inside the loop? please debug and let me know – DevelopmentIsMyPassion Dec 18 '13 at 11:55
@user3114645 you get value 45 for rs(0) inside loop 5 times? – DevelopmentIsMyPassion Dec 19 '13 at 08:51

Trying to save the recordset results into array vba

1 Answers1