Query Listing a table's field name, type, description

[WhyMeAndNotYou]

How can I access a table's field name, field type, and field description using a query?

 

[JeremyNYC]

It depeneds on what you're trying to do. You can certainly use this to list all the fields of a given table:

Sub ListTheFields(strTable As String)
Dim db As DAO.database
Dim rst As DAO.Recordset
Dim strSql As String
Dim intCount As Integer

Set db = CurrentDb
strSql = "SELECT TOP 1 * FROM " & strTable
Set rst = db.openrecordset(strSql, dbopensnapshot)
For intCount = 0 To (rst.Fields.Count - 1)
    Debug.Print rst.Fields(intCount).Name
Next intCount

rst.Close
Set rst = Nothing
db.Close
Set db = Nothing

End Sub

Just put that in a module and then in the immediate window, type this:
ListTheFields("YourTableName")

Hope this helps.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[PHV]

Take a look at TableDefs in your object browser.
Note: you must have DAO 3.x referenced.

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

 

[WhyMeAndNotYou]

I am working with someone else's database to solve a problem. You can look at the structure of a table in the database in design view, but you cannot work with the database with a table design view open. The table structure visually appears as a table with a field name, type, and description columns. It would be useful to be able to create a simple query that would list the field name, type, and description of a table. One of the useful purposes would be for documentation of an application for which the designer is no longer present. The other useful purpose is to have the table description independently viewable when reviewing the design of forms, reports, or queries in the database.

I will try the code which has been provided, but I was hoping that the objective could be achieved without coding, i.e., using a simple query.

Upon review, it appears that this post might have more correctly be submitted to another Access Forum. My apologies if it has been mis-posted.

 

[WhyMeAndNotYou]

Reply:
JeremyNYC:
Your code was instructive, but since it was essentially a list to printer using the output would involve cutting and pasting; moreover, I was unable to access the Description property of the table.

PHV:
Was unclear how to implement your suggestion. Looked at TableDefs in the object viewer, which appears to give parameters which could be used for coding. Tried to substitute some of the parameters into JeremyNYC's example but the result was mismatch variable error messages. Also as with JeremyNYC, the description property was not readily evident

 

[KenReay]

Hi

Jeremy and PHV are giving you the information, you will find all of the data relating to tables and their fields in the tabledefs() collection and its associated collections (eg Fields collection, indexes collection etc), you just need to extract it and display it using the examples they gave you.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[WhyMeAndNotYou]

Thank you for replying. I tried modifying Jeremy's code using the various subparameters available for the Fields paramater since there are no listed parameters identified as "description". Some of the options resulted in listings of various field parameter values, others resulted in errors.