Query A Database Table Name, Type, and Description 3

[WhyMeAndNotYou]

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

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.

 

[bosk00]

Its not a query but you can use the built in documentor. In access2000 its tools/analyze/documentor Pick the table you are inteseted in. Not perfect but it may help.

Alan
Senility at its finest

 

[WhyMeAndNotYou]

Thank you for the suggestion, but the solution is too kludgy to be practical. It was the initial approach to the problem and entailed running the documentor, outputting the report result to Excel, manually deleting all the rows which were not useful, coping the remaining information so that all the entries of interest for one field were on one line, and then importing the resultant Excel spreadsheet back to the database. Gotta think that there is a better way, a more direct way.

 

[glalsop]

You can use the TableDefs collection via VBA to extract this information. I'm not sure that Access SQL supports things like the SQL keyword DESCRIBE.

Anyway, something like this is how you can generate the info you are looking for:

Private Sub testing()

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = CurrentDb()
Set tdf = dbs.TableDefs("Table3")

For Each fld In tdf.Fields
Debug.Print "Name: " & fld.Name
Debug.Print "Data Type: " & fld.Type
Next fld

End Sub

I'm not sure what the field property is for 'description', but it shouldn't be to hard to find.

Good luck

-Gary

 

[TonyJollans]

Hi WhyMeAndNotYou,

It would be possible to design a system to provide what you want, but it's really re-inventing the wheel.

Why not just copy the Table (Structure only) and have the copy open in Design View? Or, as an extreme, copy the database and run a second instance of Access for reference while you work?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[WhyMeAndNotYou]

Tony: Thank you for the suggestion. It would address part of the objective but not all. First, it would not facilitate hardcopy documentation (the documenter provides either too little or too much data). Second, if the data could be obtained via a query, the query could be used to create a table that could be sorted and searched, necessary for checking out problems with the application's forms and reports. (Remember, the context is to provide assistance to a non-technical user of an application the developer of which is no longer available).

Gary: I also appreciate your suggestion. When I tried copying the code into a module window and running it in an immediate window, the attempt generated an error. It this not the correct way to implement your solution? As for the description propery, it was not readily discernable in the object viewer.

 

[WhyMeAndNotYou]

Gary: I copied your code into a module and saved the module as testing; substituted "MyTableName" for "Table3"; opened upon an immediate window, type testing <ENTER> and received the following error message: "expected variable or procedure, not module".

Microsoft KnowledgeBase Article 125773 has similar code to that which you provided, with some error trapping, but the run results are the same.

The version of Access that I am running is 2000. Does the basic code have to be modified for this version of Access?

 

[glalsop]

What error did you get? If you run it from a module, the results should show up in the Immediate Window (assuming that you replaced 'Table3' with the name of a table in your database). I just tested this code (including the description) and it runs correctly:

Private Sub testing()

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
        
    Set dbs = CurrentDb()
    Set tdf = dbs.TableDefs("Table3")
    
    On Error GoTo Err_testing
    
    For Each fld In tdf.Fields
        Debug.Print "Name: " & fld.Name
        Debug.Print "Data Type: " & fld.Type
        Debug.Print "Description: " & fld.Properties("Description")
    Next fld
    
Exit_testing:
    Exit Sub
    
Err_testing:
    If Err.Number = 3270 Then
        '3270 is the error that is thrown for missing properties...
        Resume Next
    Else
        MsgBox "Error: " & Err.Number & ": " & Err.Description
        Resume Exit_testing
    End If
    
End Sub

Be sure to implement the error catching, or errors will be generated for each field that does not contain a description.

Good luck

-Gary

 

[glalsop]

I am also running A2K...

-Gary

 

[WhyMeAndNotYou]

Gary: I want to thank you for your help.

The error is:

Microsoft Visual Basic
Compiler Error
Expected variable or procedure, not module

Since I am copying your code exactly with the replacement of only the "Table3" string, and since I do not appear to be able to access the "Descriptions" property of Fields (when I have tried to run separate code snippets), it would appear that I may not have included an appropriate Reference library in the application. The existing Available References are:

Visual Basic For Applications
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft DAO 2.5/3.51 Compatibility Library

When I previously tried to check off the Microsoft DAO 3.6 Object Library (thinking that this would be the latest version of DAO objects available to me) the action failed with a duplicate elements names error.

 

[TonyJollans]

Hi WhyMeAndNotYou,

The error message you get is nothing to do with the code; it is because of how you are trying to run it.

You have named the module "testing".
When you enter "testing" in the Immediate Pane you get an error message ..
"expected variable or procedure, not module".

You should not generally call a module and a procedure the same, but if you do, you need to refer to the procedure with qualification as [blue]testing.testing[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[WhyMeAndNotYou]

OK. I assume the format is module.procedure (where module is the external name and procedure is the internal name).

Unfortunately, the description field is still not being produced. Since the error trap for 3270 is Resume Next, I assume that the module is encountering this error when attempting to access field.properties("Description") [the leading text identifier for the Description line is not being outputted]. If this property is available to you but not to me, it would seem likely that you are accessing a Reference Library that I am not.

 

[WhyMeAndNotYou]

Tony: Ignore last post. After I exited out of everything and re-entered the database, the code worked.

Thanks for all your help, but I have one final question. The sub name is testing(), the external module name is modtesting.

In the immediate window if I try to run testing, the error message is "Expected procedure not variable"; if I try to run modtesting, the error message is "Expected variable or procedure not module". The only way it works is if I enter external_module_name.internal_subroutine_name. I thought that if the names were not the same, in the immediate window I only needed to type the internal subroutine name.

 

[glalsop]

No, it is not the references. I just switched to DAO 3.51 to make sure and all runs smoothly. The only time that the description does not get output is for fields that do not have a description.

-Gary

 

[glalsop]

That's because I have it defined as a Private subroutine. The Immediate Window can't see it. If you change the declaration to Public, you can just type testing.

-Gary

 

[WhyMeAndNotYou]

Below is the Microsoft's function code that supposedly provides similar functionality as the subroutine code that Gary has generously provided and which Tony helped me use successfully. Using a function has the advantage of being able to be included in a regular query, but when I run the code in the immediate window I get a datatype mismatch. Why?

?GetFieldDescription.GetFieldDescription("TableName", "FieldName")

Error message: Data type mismatch

"NULL" is displayed in immediate window.

Option Explicit
Function GetFieldDescription(ByVal MyTableName As String, ByVal MyFieldName As String)
Dim DB As Database
Dim TD As TableDef
Dim FLD As Field

Set DB = DBEngine.Workspaces(0).Databases(0)

On Error GoTo Err_GetFieldDescription

Set TD = DB.TableDefs(MyTableName)
Set FLD = TD.Fields(MyFieldName)

GetFieldDescription = FLD.Properties("Description")

Bye_GetFieldDescription:
Exit Function

Err_GetFieldDescription:
Beep: MsgBox Error$, 48
GetFieldDescription = Null
Resume Bye_GetFieldDescription

End Function