Build Multi-Dimension Array
I'm building an Array that is the number of records returned by 4 columns (0 thru 3):ReDim LookUpArray(Lookers.RecordCount - 1, 3) . The GetSQL instruction goes to a short procedure that opens the connection, runs the query, and returns control to this macro.
Sub MakeLookupArray()
Dim Lookers As New Recordset
Dim GetLookups As String
GetInfo = "SELECT * FROM TheTable"
GetInfo = GetInfo & " WHERE (ReportCode=" & Report & ")"
Set Lookers = getSql(GetInfo)
If Not Lookers.Fields.Count > 0 Then GoTo ProcessFailure
If Not Lookers.RecordCount > 0 Then GoTo ProcessFailure
ReDim LookUpArray(Lookers.RecordCount - 1, 3)
With Lookers
.MoveFirst
Dim A As Integer
A = 0
While Not .EOF
LookUpArray(A, 0) = Lookers.Fields("ID")
LookUpArray(A, 1) = Lookers.Fields("Classification")
LookUpArray(A, 2) = Report
LookUpArray(A, 3) = Date
A = A + 1
.MoveNext
Wend
.Close
GetLookups = True
End With
CloseAndExit:
Set Lookers = Nothing
Exit Sub
ProcessFailure:
MsgBox "Selection of Get Data failed; Contact Support.", vbCritical
End Sub
In the end, you have all the data you need to extract data, etc from the array.
|