Have a look at the Recordset object's method called GetRows i.e. you
don't need to loop through the recordset to get an array of table
names. Below is some example code. Also, you don't need to instantiate
('create') the recordset with the new keyword; OpenSchema creates the
recordset and returns a reference/pointer to it:

Dim TableList As ADODB.Recordset ' New keyword not required
Dim TArray As Variant
Set TableList = cnnConn.OpenSchema(adSchemaTables, _
Array(Empty,Empty, Empty, "Table"))
TArray = TableList.GetRows(, , "TABLE_NAME")
MyListBox.List = Excel.Application.Transpose(TArray)

--

(masayoshi hayashi) wrote in message . com...
Thanks onedaywhen. I have searched online further and wrote:

' List table names in the database file to LbTable listbox in
UFSelectTable userform.

Dim TableList As New adodb.Recordset
Set TableList = cnnConn.OpenSchema(adSchemaTables, Array(Empty,
Empty, Empty, "Table"))
While Not TableList.EOF
NFound = NFound + 1
ReDim Preserve TArray(NFound)
TArray(NFound) = TableList!TABLE_NAME
TableList.MoveNext
Wend

Yes the table array is passed to a listbox. :) The following link was
useful too.
http://support.microsoft.com/support.../Q186/2/46.ASP


I'll work out for excel files too as the code in my first post would
not work for excel files.