select table and columns name

[Guoqi Zheng]

Dear sir,

For a database, at first, I need to select all the table name in the
database, then I need to select columns name for any table user select. This
is to make an import and export tools between two databases.

How can I do this? In Ms Sql, they have something like sysobject table, etc.
When can I find the system catalog database of ms access?


Thanks in advanced.

 

[Graham R Seach]

You can return table names using the following query:
SELECT [Name] FROM MSysObjects
WHERE [Type] = 1
AND Left([Name], 4) <> "MSys"
AND Left([Name], 1) <> "~"

You can return the table's fieldnames using the following code:
Dim tdf As DAO.TableDefs
Dim fld As DAO.Field

Set tdf = DBEngine(0)(0).TableDefs("sometablename")
For Each fld in tdf
Debug.Print fld.Name
Next fld

If you want to use the same strategy for returning table names as for
returning field names:
Dim tdf As DAO.TableDef

For Each tdf in DBEngine(0)(0).TableDefs
If Left(tdf.Name, 3) <> "MSys" Then
If Left(tdf.Name, 1) <> "~" Then
Debug.Print tdf.Name
End If
End If
Next tdf

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia