Creating an Array from A Sql Statement

[Guest]

Hey folks,
I am looking for a way to create a two dimension array from a SQl
Statement. this is what i have:

sql = "SELECT module.course_id, module.module_num, results.module_version,
results.student_answers, results.correct_answers" _
& " FROM module INNER JOIN results ON module.module_id =
results.module_id " _
& "WHERE module.course_id = " & selCourse & _
" AND module.module_num = " & selModule & _
" AND results.module_version = " & selVersion

rs.Open sql

'Create an array with 5 columns and 100 rows
Dim Results(1 To 100, 1 To 5) As String
Dim r As Integer
For r = 1 To rs.RecordCount
Results(r, 1) = rs.Fields("course_id")
Results(r, 2) = rs.Fields("module_num")
Results(r, 3) = rs.Fields("module_version")
Results(r, 4) = rs.Fields("student_answers")
Results(r, 5) = rs.Fields("correct_answers")
Next r

am i missing something in my array? did i use rs.RecordCount correctly?

 

[Tim Zych]

If Not rs.EOF Then
Dim Results(1 to rs.RecordCount, 1 to 5)
Dim n as Long
rs.MoveFirst
Do While Not rs.EOF
n= n+ 1
Results(n, 1) = rs.Fields("course_id")
Results(n, 2) = rs.Fields("module_num")
Results(n 3) = rs.Fields("module_version")
Results(n, 4) = rs.Fields("student_answers")
Results(n, 5) = rs.Fields("correct_answers")
rs.MoveNext
Loop
End If


Another option is to copy it directly to the array using GetRows.

HOWTO: Use a Fields Array in the Fields Option of GetRows Method
http://support.microsoft.com/kb/189360/en-us

The article is geared to using an optional parameter, but the rest of the
example is good (omit the optional part if you want).