Get table names from access through vba in excel

  • Thread starter Thread starter Danny
  • Start date Start date
D

Danny

Is it possible to get the table names from an access mdb
though excel's vba?
I am trying to populate a combo box in excel with the
names of table from this access mdb that I have.
I tried many methods I found online but I have not been
successful.

Thanks.
 
Danny said:
Is it possible to get the table names from an access mdb
though excel's vba?
I am trying to populate a combo box in excel with the
names of table from this access mdb that I have.
I tried many methods I found online but I have not been
successful.
Click to expand...

Hi Danny,

This works for me. It requires you to set a reference to the Microsoft
ADO Ext. 2.X for DDL and Security object library as well as the normal ADO
object library.

Sub GetTableNames()

Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim lRow As Long
Dim szConnect As String

Sheet1.UsedRange.Clear

szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\MyDatabase.mdb;"

Set cnn = New ADODB.Connection
cnn.Open szConnect
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cnn

lRow = 1
For Each tbl In cat.Tables
Sheet1.Cells(lRow, 1).Value = tbl.Name
lRow = lRow + 1
Next tbl

cnn.Close
Set cat = Nothing
Set cnn = Nothing

End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
 
This works for me. It requires you to set a reference to the Microsoft
ADO Ext. 2.X for DDL and Security object library as well as the normal ADO
object library.
Click to expand...

The ADODB OpenSchema has a slight advantage in that you can specify to
exclude system tables, requires only one ADO library (actually, Rob's
only requires ADOX), plus you get a recordset object to work with e.g.
can Sort, Filter, GetRows, etc. Modified Rob's code:

Option Explicit

Sub GetTableNames()

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim lRow As Long
Dim szConnect As String

Sheet1.UsedRange.Clear

szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Tempo\New_Jet_DB.mdb;"

Set cnn = New ADODB.Connection
cnn.CursorLocation = adUseClient
cnn.Open szConnect

Set rs = cnn.OpenSchema( _
adSchemaTables, _
Array(Empty, Empty, Empty, "Table"))

With Sheet1
.Range(.Range("A1"), _
.Range("A1").Cells(rs.RecordCount)) _
.Value = Application.Transpose( _
rs.GetRows(, , "TABLE_NAME"))
End With

rs.Close
cnn.Close
End Sub


Jamie.

--
 
Back
Top