How can I get all table names from MS Access databse

[Mlaky]

I'm using MS Access and VB. I need to get all table names from database. How
can I do that?


Thank you.

 

[Bob Phillips]

Mlaky,

This should do what you want

Sub DBTables()
Dim oConn As Object
Dim oCat As Object
Dim oTable As Object
Dim sConnString As String
Dim sFileName As String

sFileName = "D:\Development\vb\hospital db\TBIcontacts.mdb"

sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFileName & ";"

Set oConn = CreateObject("ADODB.Connection")
On Error Resume Next
oConn.Open sConnString
If Err.Number <> 0 Then
MsgBox "Error reading file " & sFileName
Else
On Error GoTo 0
Set oCat = CreateObject("ADOX.Catalog")
Set oCat.ActiveConnection = oConn

For Each oTable In oCat.Tables
If Left(oTable.Name, 4) <> "MSys" Then _
Debug.Print oTable.Name
Next oTable
End If

oConn.Close
Set oCat = Nothing
Set oConn = Nothing

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[onedaywhen]

Just for interest, here's a variation which uses ADO only (i.e. not
ADOX) and means you don't have to parse the table names to identify
for system tables:

Sub odwDBTables()
Dim oConn As Object
Dim oRS As Object
Dim sConnString As String
Dim sFileName As String

sFileName = "D:\Development\vb\hospital db\TBIcontacts.mdb"

sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFileName & ";"

Set oConn = CreateObject("ADODB.Connection")
On Error Resume Next
oConn.Open sConnString
If Err.Number <> 0 Then
MsgBox "Error reading file " & sFileName
Else
On Error GoTo 0
Set oRS = oConn.OpenSchema(adSchemaTables, _
Array(Empty, Empty, Empty, "Table"))
Do While Not oRS.EOF

Debug.Print oRS!TABLE_NAME.Value
oRS.MoveNext

Loop
End If

oConn.Close
Set oRS = Nothing
Set oConn = Nothing

End Sub

--

 

[onedaywhen]

....maybe not purely for interest, there may be functionality benefits.
I previously used ADOX exclusively for schema info, then there was one
day when ADOX could not return the schema info I need (primary keys
and unique indices) from a lesser known DBMS (Intersystems Caché,
anyone?) I searched round for an alternative and unexpectedly found it
in the ADO object model, being OpenSchema. It's very functionally
rich: take a look at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcstschemaenum.asp