Values of a Table into an array 1

[krymat]

How would I get the values of a table, say 8 columns by 30 rows, into an array?

 

[BlackKnight]

Hi,
This example will suit your needs. Modify as needbe.

Public Sub GetRowsX()

Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
Dim strMessage As String
Dim intRows As Integer
Dim avarRecords As Variant
Dim intRecord As Integer

' Open recordset with names and hire dates from employee table.
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
Set rstEmployees = New ADODB.Recordset
rstEmployees.Open "SELECT fName, lName, hire_date " & _
"FROM Employee ORDER BY lName", strCnn, , , adCmdText

Do While True
' Get user input for number of rows.
strMessage = "Enter number of rows to retrieve."
intRows = Val(InputBox(strMessage))

If intRows <= 0 Then Exit Do

' If GetRowsOK is successful, print the results,
' noting if the end of the file was reached.
If GetRowsOK(rstEmployees, intRows, _
avarRecords) Then
If intRows > UBound(avarRecords, 2) + 1 Then
Debug.Print &quot;(Not enough records in &quot; & _
&quot;Recordset to retrieve &quot; & intRows & _
&quot; rows.)&quot;
End If
Debug.Print UBound(avarRecords, 2) + 1 & _
&quot; records found.&quot;

' Print the retrieved data.
For intRecord = 0 To UBound(avarRecords, 2)
Debug.Print &quot; &quot; & _
avarRecords(0, intRecord) & &quot; &quot; & _
avarRecords(1, intRecord) & &quot;, &quot; & _
avarRecords(2, intRecord)
Next intRecord
Else
' Assuming the GetRows error was due to data
' changes by another user, use Requery to
' refresh the Recordset and start over.
If MsgBox(&quot;GetRows failed--retry?&quot;, _
vbYesNo) = vbYes Then
rstEmployees.Requery
Else
Debug.Print &quot;GetRows failed!&quot;
Exit Do
End If
End If

' Because using GetRows leaves the current
' record pointer at the last record accessed,
' move the pointer back to the beginning of the
' Recordset before looping back for another search.
rstEmployees.MoveFirst
Loop

rstEmployees.Close

End Sub

Public Function GetRowsOK(rstTemp As ADODB.Recordset, _
intNumber As Integer, avarData As Variant) As Boolean

' Store results of GetRows method in array.
avarData = rstTemp.GetRows(intNumber)
' Return False only if fewer than the desired
' number of rows were returned, but not because the
' end of the Recordset was reached.
If intNumber > UBound(avarData, 2) + 1 And _
Not rstTemp.EOF Then
GetRowsOK = False
Else
GetRowsOK = True
End If

End Function

Have a good one!
BK

 

[BlackKnight]

Hi again,

That last solution is the elegant way, the less elegant method is to simply cycle through the recordset and assign each field into a matrix where rst is a recordset, and Row is each record and Col is each field in a record


dim Recs (30, 8) as variant
dim Col as integer, Row as integer

Row=0
do while not rst.eof
Row = Row+1
for Col=1 to 8
Recs(Row, Col) = rst.fields(Col).value
next Col
rst.movenext
loop
rst.close

Have a good one!
BK