List of Table Names in DB

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thanks for taking the time to read my question.

Just wondering how to return the names and descriptions of all the tables in
the active database using code.

Thanks,

Brad
 
Sub ListTables()
On Error GoTo Err_ListTables

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim strDescription As String

Set dbCurr = CurrentDb
For Each tdfCurr In dbCurr.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
strDescription = tdfCurr.Properties("Description")
Debug.Print tdfCurr.Name & ": " & strDescription
End If
Next tdfCurr

End_ListTables:
Set dbCurr = Nothing
Exit Sub

Err_ListTables:
If Err.Number = 3270 Then ' Property Not Found
strDescription = "*** No Description found ***"
Resume Next
Else
MsgBox Err.Number & ": " & Err.Description
Resume End_ListTables
End If

End Sub


The problem is that the Description property doesn't exist unless a
description has been assigned to the table. That's the reason for the odd
error trapping.
 
Table names are easy. You can use a simple query:
SELECT DISTINCT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=1));

Table descriptions are a little problematic because unless a table actually
has a description, the Description property doesn't exist for the table.
(Wierd, I know).

Create a function called TableDescription as follows:
'*********************
Function TableDescription(TableName As Variant) As String
'NOTE: this function requires a Reference to DAO.

On Error GoTo Err_TableDescription
Dim db As DAO.Database
Dim con As DAO.Container
Dim doc As DAO.Document
Dim prp As DAO.Property
Set db = CurrentDb()
Set con = db.Containers("Tables")
Set doc = con.Documents(TableName)
Set prp = doc.Properties("description")

TableDescription = prp.Value
Exit_TableDescription:
Exit Function

Err_TableDescription:
If Err.Number = 3270 Then
TableDescription = "There is no description for this Table"
Resume Exit_TableDescription
Else
MsgBox Err.Description
Resume Exit_TableDescription
End If
End Function
'*********************

Then modify your query as follows:
SELECT DISTINCT MSysObjects.Name, TableDescription([Name]) AS Description
FROM MSysObjects
WHERE (((MSysObjects.Type)=1));

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Hi Brad,

I think you'll need DAO for this (also you'll have to set the reference to
DAO's library in the VB editor).

A take might be:

Sub GetTableNames()

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim prp As DAO.Property
Dim strTblProp As String

Set dbs = CurrentDb()
For Each tdf In dbs.TableDefs
On Error Resume Next
' If the Property "Description" doesn't exist for a Table,
' the next line will generate an Error
Set prp = tdf.Properties("Description")
strTblProp = prp.Name
If Err <> 0 Then strTblProp = "None"
On Error GoTo 0
Debug.Print "Table Name: " & tdf.Name & " - Description: " & strTblProp
Next
Set prp = Nothing
Set tdf = Nothing

End Sub

This routine will print all the TableNames in the Immediate window.
It will also give you the names of some system Tables (like
MSysAccessObjects etc.).

You could do all sorts of things withing the For-Each loop, like insert the
Table Names in a VBA array and use this array to enter the Table Names in a
new Table with SQL ("INSERT INTO...").

Hope this is of help.
Alex
 
Clever, Roger!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Roger Carlson said:
Table names are easy. You can use a simple query:
SELECT DISTINCT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=1));

Table descriptions are a little problematic because unless a table
actually
has a description, the Description property doesn't exist for the table.
(Wierd, I know).

Create a function called TableDescription as follows:
'*********************
Function TableDescription(TableName As Variant) As String
'NOTE: this function requires a Reference to DAO.

On Error GoTo Err_TableDescription
Dim db As DAO.Database
Dim con As DAO.Container
Dim doc As DAO.Document
Dim prp As DAO.Property
Set db = CurrentDb()
Set con = db.Containers("Tables")
Set doc = con.Documents(TableName)
Set prp = doc.Properties("description")

TableDescription = prp.Value
Exit_TableDescription:
Exit Function

Err_TableDescription:
If Err.Number = 3270 Then
TableDescription = "There is no description for this Table"
Resume Exit_TableDescription
Else
MsgBox Err.Description
Resume Exit_TableDescription
End If
End Function
'*********************

Then modify your query as follows:
SELECT DISTINCT MSysObjects.Name, TableDescription([Name]) AS Description
FROM MSysObjects
WHERE (((MSysObjects.Type)=1));

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




Brad said:
Thanks for taking the time to read my question.

Just wondering how to return the names and descriptions of all the tables in
the active database using code.

Thanks,

Brad
Click to expand...
Click to expand...
 
Back
Top