DAO Object Syntax, List of DB tables and fields.

[bblekfeld]

I would like to loop the below code to go through a list of tables, but when I try to define the current table as a string and pass that varible into the
Set tjTab =tjDB.TableDefs!MYCURRENTTABLENAME command
I get a syntax error.
(or, if anyone knows a really easy/clean way to get a list of all the fields in all the tables in your DB, please let me know)
Public Sub ListTable()

Dim tjDb As DAO.Database
Dim tjTab As DAO.TableDef
Dim tjFld As DAO.Field
Dim TableString As String

Set tjDb = CurrentDb
Set tjTab = tjDb.TableDefs!ORDERINFO

For Each tjFld In tjTab.Fields
Debug.Print tjFld.Name ' Do what you want with the name(s) here
Next

tjDb.Close

Set tjFld = Nothing
Set tjTab = Nothing
Set tjDb = Nothing

End Sub

 

[jebry]

Hi!

You can do it with a query:

Select Name From MSysObjects Where Type = 1 And Left(Name, 4) <> "MSys"

You can display a list of fields, if that is what you need to do, in a list box by setting the RowSource to the name of a table or query and setting the RowSourceType to Field List.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[PHV]

Something like this ?
Set tjDb = CurrentDb
For Each tjTab In tjDb.TableDefs
Debug.Print "Table: " & tjTab.Name
For Each tjFld In tjTab.Fields
Debug.Print tjFld.Name
Next
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bblekfeld]

Thanks!!!

Okay, so that works better now, BUT, when I try to reference tjfld.name in the INSERT INTO SQL Statement, it wants me to define each Field name as if it were a variable, instead of inserting the actual field name into the table.


See DoCmd. Statement


Public Sub ListTables()

Dim tjDB As DAO.Database
Dim tjTab As DAO.TableDef
Dim tjFld As DAO.Field
Set tjDB = CurrentDb
DoCmd.SetWarnings False
For Each tjTab In tjDB.TableDefs
Debug.Print "Table: " & tjTab.Name

For Each tjFld In tjTab.Fields
Debug.Print tjFld.Name
DoCmd.RunSQL ("INSERT INTO TablesQueriesReportsList ([FieldName]) VALUES (" & tjFld.Name & ")")
Next
Next
DoCmd.SetWarnings True

Set tjFld = Nothing
Set tjTab = Nothing
Set tjDB = Nothing
End Sub

 

[PHV]

Have you tried to replace this:
VALUES (" & tjFld.Name & ")")
by this:
VALUES ('" & tjFld.Name & "')")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bblekfeld]

thank you! those darn quotes get so complicated sometimes.