PRINT / EXPORT the Design View of a Table

I'm looking at the design view for a table with

Field Name
Data Type
Description

I want to select all and EXPORT this information to an Excel spreadsheet, or perhaps a comma-delimited file.

I also want to print a hard copy of this information.

HOW can I do this?

Thank you,

Tom
LVL 5
Tom KnowltonWeb developerAsked:
Who is Participating?
 
PsychoDazeyConnect With a Mentor Commented:
Well, you seem to be pretty advanced for just starting a few weeks ago.  VB and VBA are both extremely user friendly, if you get into C++ or VBscript, you will appreciate this even more.  I myself am a lot less experienced then most on EE, Im an engineer by degree, but started programming about 4 years ago and quickly switched careers.  Let me know if you need more help.  plmnesz@aol.com
0
 
PsychoDazeyCommented:
Go to the Database window, click on Tools, Analyze, Documenter.  This will allow you to select which tables/forms/queries, etc. you want to see.  You can also specify how much info you want included under the options tab.
0
 
hotbudareCommented:
Use this as a starting point:

'BOC *************************************
Sub ShowDataInfo()
Dim db As DAO.Database
Dim tdef As DAO.TableDef
Dim prp As DAO.Property
Dim fld As DAO.Field

    Set db = CurrentDb
    For Each tdef In db.TableDefs
        Debug.Print "Table: " & tdef.Name
        Debug.Print String(Len(tdef.Name) + 7, "=")
        For Each fld In tdef.Fields
            Debug.Print "Field: " & fld.Name
            For Each prp In fld.Properties
                If prp.Name <> Name Then
                    On Error Resume Next
                    Debug.Print Space(4) & prp.Name & ": " & prp.Value
                    If Err <> 0 Then
                        Debug.Print Space(4) & prp.Name & ": <Not printable>"
                    End If
                    On Error GoTo 0
                End If
            Next prp
            Debug.Print
        Next fld
    Next tdef
    db.Close
    Set db = Nothing
End Sub

Sub PrintDesign()
Dim db As DAO.Database
Dim tdef As DAO.TableDef
Dim fld As DAO.Field
Dim strDescription As String

    Set db = CurrentDb
    For Each tdef In db.TableDefs
        Debug.Print "Table: " & tdef.Name
        Debug.Print String(Len(tdef.Name) + 7, "=")
        For Each fld In tdef.Fields
            Debug.Print "*Field: " & fld.Name
            Debug.Print " -FieldType  : " & FieldTypeName(fld.Type)
            On Error Resume Next
            strDescription = fld.Properties("Description")
            If Error <> 0 Then
                strDescription = "<Not available>"
            End If
            On Error GoTo 0
            Debug.Print " -Description: " & strDescription
            Debug.Print
        Next fld
    Next tdef
    db.Close
    Set db = Nothing
End Sub

Function FieldTypeName(FieldType As DAO.DataTypeEnum) As String

    Select Case FieldType
    Case dbBigInt
        FieldTypeName = "dbBigInt"
    Case dbBinary
        FieldTypeName = "dbBinary"
    Case dbBoolean
        FieldTypeName = "dbBoolean"
    Case dbByte
        FieldTypeName = "dbByte"
    Case dbChar
        FieldTypeName = "dbChar"
    Case dbCurrency
        FieldTypeName = "dbCurrency"
    Case dbDate
        FieldTypeName = "dbDate"
    Case dbDecimal
        FieldTypeName = "dbDecimal"
    Case dbDouble
        FieldTypeName = "dbDouble"
    Case dbFloat
        FieldTypeName = "dbFloat"
    Case dbGUID
        FieldTypeName = "dbGUID"
    Case dbInteger
        FieldTypeName = "dbInteger"
    Case dbLong
        FieldTypeName = "dbLong"
    Case dbLongBinary
        FieldTypeName = "dbLongBinary"
    Case dbMemo
        FieldTypeName = "dbMemo"
    Case dbNumeric
        FieldTypeName = "dbNumeric"
    Case dbSingle
        FieldTypeName = "dbSingle"
    Case dbText
        FieldTypeName = "dbText"
    Case dbTime
        FieldTypeName = "dbTime"
    Case dbTimeStamp
        FieldTypeName = "dbTimeStamp"
    Case dbVarBinary
        FieldTypeName = "dbVarBinary"
    End Select
End Function
'EOC *************************************

HTH/EQTA
T.S.U. Mario Osorio
Punto Fijo, falcon, Venezuela

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
hotbudareCommented:
Ooops!

Forget about the ShowDataInfo() Sub ShowDataInfo, I used that for another example here ...
0
 
Tom KnowltonWeb developerAuthor Commented:
hotbudare:

What exactly does your code do, and what are the advantages over the Analyze...Documenter approach?

Thanks,

Tom
0
 
Tom KnowltonWeb developerAuthor Commented:
PsychoDazey:

Your answer has opened up some doors for me, thank you.

Once the Documentor generates the report, you can right-click on the report and Export the report under several file formats, including Excel.

Thanks!
0
 
PsychoDazeyCommented:
You're welcome, glad I could help.  You can also automate this if you are going to be doing this frequently.
0
 
Tom KnowltonWeb developerAuthor Commented:
How can I automate this?

I anticipate I will be doing this probably once a month.

The reason I am doing this is some of the Field Names are not very descriptive of what they are.  I don't want to have to guess what the field is for.

Basically, I am trying to create a data dictionary for END USERS...very simple to understand, that describes the database.

Thanks
0
 
Tom KnowltonWeb developerAuthor Commented:
One thing the Documenter seems to lack is printing the Description from the Database Design View for a table.

This is the most important part of what I am doing?

Did I miss something in the Documenter options???

Thanks,

Tom
0
 
PsychoDazeyCommented:
I have not used it myself, but you can enter this command in the on_click event for a command button:
docmd.RunCommand acCmdAnalyzeTable
0
 
PsychoDazeyCommented:
Yes, you can print.  My Access is acting up right now so I cant run the documenter, but I have used it to print several times before.
0
 
Tom KnowltonWeb developerAuthor Commented:
Really?

Cool!
0
 
PsychoDazeyCommented:
I actually used it for the same function as you.  I built a book for the database(s) I built in case someone else had to do some work they would have a handy reference.
0
 
Tom KnowltonWeb developerAuthor Commented:
PsychoDazey:

Cool!

Yeah, I just started working in MS Access / VBA a few weeks ago, and I am quickly realizing that I need something like a data dictionary to make somethings easier for me.

Thanks,

Tom
0
 
Tom KnowltonWeb developerAuthor Commented:
PsychoDazey:

I've taken some classes in school that talk about building a data dictionary if you are doing database work.

I am actually a database development newbie, especially in Access.

Before this job, I was working in a third party database called CodeBase, which is dBase IV / FoxPro compatible.  I accessed the db via the CodeBase API (in Delphi).  I did that for about 10 months before starting this job using Access.

Thanks again for your help.

Tom
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.