Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an Array from A Sql Statement
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an Array from A Sql Statement
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" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a dynamic IF statement | Excel Worksheet Functions | |||
If Statement / Array | Excel Worksheet Functions | |||
Creating If Statement not to include if O in average | Excel Discussion (Misc queries) | |||
Creating an IF / AND statement | Excel Worksheet Functions | |||
Need help creating a case statement | Excel Programming |