recordset to array

[casey]

My code below works, not sure if its the right way. But my goal is to make
my code efficient.
A little background. I have a bunch of code that takes data from Access and
outputs to Excel. The XL file contains up to 12 worksheets. My goal is to
create a table listing all the names of the worksheet, save to an array.
Currently I am using a SELECT CASE statement to itemize the 12 worksheets.

I need to be able to capture the id and sheet_name from the TABLE_LISTING.

Here is my code:
==============
Function test()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim arrayReports As Variant
Dim intNumOfReports As Integer, i As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("select id, sheet_name from REPORT_LISTING",
dbOpenSnapshot)
intNumOfReports = DCount("*", "REPORT_LISTING")

rs.MoveFirst
ReDim arrayReports(intNumOfReports)
For i = 1 To intNumOfReports
arrayReports(i) = rs.Fields("sheet_name")
rs.MoveNext
Next i

For i = LBound(arrayReports) To UBound(arrayReports)
Debug.Print arrayReports(i)
Next i

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

End Function

 

[casey]

never mind, I figured out the code.

 

[Guest]

dim rs as dao.recordset
dim arrayReports

arrayReports = rs.GetRows(intNumOfReports)

(david)