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


Thank you.

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)

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


Thank you.

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

--

"Bob Phillips" wrote in message ...
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)

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


Thank you.

....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/de...schemaenum.asp

--

(onedaywhen) wrote in message . com...
Just for interest, here's a variation which uses ADO only (i.e. not
ADOX)...