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).
--
Tim Zych
SF, CA
"Bryan" <(E-Mail Removed)> wrote in message
news:4B6C3AA5-27E3-42BD-8B0F-(E-Mail Removed)...
> 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?
Similar Threads
