retrieving table names from Excel and Access sources using ADO

[masayoshi hayashi]

I have created ADO connection sub below but I want to generate a list
of tables in the selected file, so that users don't have to know table
names of the selected Excel or Access file in advance. If you could
give me some hints or links for further study of excel ado, I would
be appreciated.

(undefined variables and objects are defined elsewhere.)

Sub ConnectToDB()
Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
Dim DBFileName As Variant
Dim TableName As String
Dim FilterList As String

' Get an access filename from wherever you want.
FilterList = "Access Files (*.mdb),*.mdb, " & "Excel Files
(*.xls),*.xls"
DBFileName = Application.GetOpenFilename(FileFilter:=FilterList,
Title:="Database File")

' Exit if GetOpenFilename dialog box canceled
If DBFileName = False Then
MsgBox "No file was selected."
Cancel = True
Exit Sub
End If

' Request a table name in the DBFileName
Do Until TableName <> ""
TableName = InputBox("Enter a table name in the database " &
DBFileName)
Loop

' Open a connection to the database file.
Set cnnConn = New ADODB.Connection
With cnnConn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0"
.Open DBFileName
End With

' SQL query to the Access database
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
.CommandText = "Select * From " & TableName
.CommandType = adCmdText
.Execute
End With

' Open the TableName table as a recordset.
Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn
rstRecordset.Open cmdCommand

' Create a pivot cache
Set PC = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
Set PC.Recordset = rstRecordset

Call CrossTabConnect

' Close the connection to the database.
cnnConn.Close
Set cmdCommand = Nothing
Set rstRecordset = Nothing
Set cnnConn = Nothing
End Sub

 

[onedaywhen]

The following should give you a recordset of table infomation for the
connection:

Dim rstTables As ADODB.Recordset
Set rstTables = cnnConn.OpenSchema(adSchemaTables)

I'll leave it for you to look at the field names/values returned and
to decide how to present them for the user to choose a table name
(combobox on a userform, perhaps?)

 

[masayoshi hayashi]

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/kb/articles/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.

 

[onedaywhen]

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)