Table Fieldnames (I need your help, please)

[jjones]

Hello, I have a question.

I want to be able to display, query, list, or etc. for all
the field names and data elements associated for a table
in Access 97 called tbl_libraryPatrons. I know Access 97
has a feature called documenter. However, I wanted to able
to do this through code.


How do I do it?

 

[Dave]

I use something like this to , hope it helps.
Once the code has un then you can create a
report on it or use a query or whatever you want
------
tbl_TableList has 3 Fields
TableName Text 'This is the Name of the Table
RecordCount Text 'This is the numbe of records in the
table
FieldName Text 'This is the List of Field Names of
the table
------

Private Sub cmdListTables_Click()

Dim tdfLoop As TableDef
Dim i As Integer
Dim varstr as String
Dim db as Database
Dim rs as Recordset

varstr = "DELETE * FROM tbl_TableList"
DoCmd.RunSQL varstr

Set db = CurrentDb()
With db

' Enumerate TableDefs collection.
For Each tdfLoop In .TableDefs

For i = 0 To tdfLoop.Fields.Count - 1
varstr = "INSERT INTO tbl_TableList (TableName, RecordCound,
FieldName) VALUES ('"
varstr = varstr & tdfLoop.Name & "','" &
tdfLoop.Fields.Count & "','" & tdfLoop.Fields(i).Name & "');"
DoCmd.RunSQL varstr
Next i

Next tdfLoop

End With

DoCmd.OpenReport "rpt_TableList", acViewPreview

End If

End Sub

 

[Anon]

Thanks for your help so far. You have helped a lot. I just
have one more question.

How can I display the data type of each field name in the
table?

So for tbl_libraryPatron I could have a report that reads


FieldName Data type
PatronID Number
BookCheckedout Number
BookDue Date
BookTitle Text


Thanks

 

[Dave]

add the following to the query

tdfLoop.Fields(i).Type

this returns the constant value for the type

3 = dbinteger
10 = dbtext
8 = dbdate
5 = dbcurrency
4 = dblong
and so on

you can find out the rest youself
good luck