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.