Code to loop through each table in a database and retrieve the tables' name

  • Thread starter Thread starter LisaB
  • Start date Start date
L

LisaB

What is the proper way to loop through each tables in the database to
retrieve the Table Names

I want the code to look something like this
-------------------------
Set TheDB = CurrentDb

For Each Table In TheDB
NameTableName = Table.Name

Set TheTable = TheDB.TableDefs(NameTableName)

Run some code here

Next Table
 
What is the proper way to loop through each tables in the database to
retrieve the Table Names

I want the code to look something like this
-------------------------
Set TheDB = CurrentDb

For Each Table In TheDB
NameTableName = Table.Name

Set TheTable = TheDB.TableDefs(NameTableName)

Run some code here

Next Table
Click to expand...

Des this help?

Public Sub GetFieldNames()
Dim dbs As Dao.Database, tdf As TableDef, fld As Field

Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Left(tdf.Name, 4) <> "MSYS" Then
Debug.Print tdf.Name
For Each fld In tdf.Fields
Debug.Print " " & fld.Name
Next fld
End If
Next tdf
Set dbs = Nothing
End Sub
 
I tried to use this code in my Access 2002 db and got an error "Type
Mismatch"
on the statement below:
I don't know enough to tell why this is happening.
I'd also like to print out the field's datatype too if possible.

Can you help?
Thanks
Jim

fredg said:
Des this help?

Public Sub GetFieldNames()
Dim dbs As Dao.Database, tdf As TableDef, fld As Field

Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Left(tdf.Name, 4) <> "MSYS" Then
Debug.Print tdf.Name
Click to expand...
----------------Error Statement Below--------------------------------------
For Each fld In tdf.Fields
Click to expand...
----------------Error Statement Above--------------------------------------
 
Dim dbs As Dao.Database, tdf As TableDef, fld As Field

Dim dbs As Dao.Database, tdf As dao.TableDef, fld As dao.Field

alternatively, (not using a reference to the dao typelibrary):
Dim dbs As object, tdf As object, fld As object
I'd also like to print out the field's datatype too if possible.
Click to expand...
obviously,
debug.print fld.type
but that only gives you a number. No string values are provided for
this: you will have to translate. You could use a case statement:

case dao.dbBoolean: "Boolean"
case dao.dbDate: "Date/Time"
case dao.dbInteger: "Integer"

etc


(david)


Jim Shaw said:
I tried to use this code in my Access 2002 db and got an error "Type
Mismatch"
on the statement below:
I don't know enough to tell why this is happening.
I'd also like to print out the field's datatype too if possible.

Can you help?
Thanks
Jim

fredg said:
Des this help?

Public Sub GetFieldNames()
Dim dbs As Dao.Database, tdf As TableDef, fld As Field

Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Left(tdf.Name, 4) <> "MSYS" Then
Debug.Print tdf.Name
Click to expand...
----------------Error Statement Below--------------------------------------
For Each fld In tdf.Fields
Click to expand...
----------------Error Statement Above--------------------------------------
Click to expand...
 
Thanks! To give something back, here is what I did with your code:
Jim
---------------------------------Cut Here-----------------------------
Public Sub GetFieldNames()
' Builds a table of information about tables
' Output table record contains:
' Table Name
' Field Name
' Field's Data Type
' The output table can then be used to drive a report on the printer.
' Naming convention for tables has "tbl" as the name prefix. This is
' used to ignore system tables.

Dim dbs As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field
Dim Rt As DAO.Recordset
Set dbs = CurrentDb()
Set Rt = dbs.OpenRecordset("tblTableDefs") ' Output table

' Clean out old data in the table
Dim strSQL As String
strSQL = "DELETE * from tblTableDefs"
DoCmd.RunSQL strSQL

With Rt
For Each tdf In dbs.TableDefs
If Left(tdf.Name, 3) = "tbl" Then 'only process application
tables
For Each fld In tdf.Fields
.AddNew
!TableName = tdf.Name
!FieldName = fld.Name
Select Case fld.Type
Case DAO.dbBoolean
!DataType = "Boolean"
Case DAO.dbDate
!DataType = "Date"
Case DAO.dbBigInt
!DataType = "BigInt"
Case DAO.dbBinary
!DataType = "Binary"
Case DAO.dbByte
!DataType = "Byte"
Case DAO.dbChar
!DataType = "Char"
Case DAO.dbAutoIncrField
!DataType = "AutoIncrField"
Case DAO.dbCurrency
!DataType = "Currency"
Case DAO.dbDouble
!DataType = "Double"
Case DAO.dbFloat
!DataType = "Float"
Case DAO.dbInteger
!DataType = "Integer"
Case DAO.dbLong
!DataType = "Long"
Case DAO.dbMemo
!DataType = "dbMemo"
Case DAO.dbNumeric
!DataType = "Numeric"
Case DAO.dbSingle
!DataType = "Single"
Case DAO.dbText
!DataType = "Text"
Case DAO.dbTime
!DataType = "Time"
Case DAO.dbTimeStamp
!DataType = "TimeStamp"
Case DAO.dbVarBinary
!DataType = "VarBinary"
Case DAO.dbVariableField
!DataType = "VariableField"
End Select
.Update
Next fld
End If
Next tdf
End With
Set dbs = Nothing
Rt.Close
Set Rt = Nothing
End Sub
 
Yes this helps. Thank You

fredg said:
Des this help?

Public Sub GetFieldNames()
Dim dbs As Dao.Database, tdf As TableDef, fld As Field

Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Left(tdf.Name, 4) <> "MSYS" Then
Debug.Print tdf.Name
For Each fld In tdf.Fields
Debug.Print " " & fld.Name
Next fld
End If
Next tdf
Set dbs = Nothing
End Sub
Click to expand...
 
Back
Top