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

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
 
F

fredg

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

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
 
J

Jim Shaw

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
----------------Error Statement Below--------------------------------------
For Each fld In tdf.Fields
----------------Error Statement Above--------------------------------------
 
D

david epsom dot com dot au

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.
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
----------------Error Statement Below--------------------------------------
For Each fld In tdf.Fields
----------------Error Statement Above--------------------------------------
 
J

Jim Shaw

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
 
L

LisaB

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top