Solved

PRINT /  EXPORT the Design View of a Table

Posted on 2001-08-10
15
297 Views
Last Modified: 2012-05-04
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
0
Comment
Question by:knowlton
  • 7
  • 6
  • 2
15 Comments
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 6373586
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
 
LVL 2

Expert Comment

by:hotbudare
ID: 6373643
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
 
LVL 2

Expert Comment

by:hotbudare
ID: 6373655
Ooops!

Forget about the ShowDataInfo() Sub ShowDataInfo, I used that for another example here ...
0
 
LVL 5

Author Comment

by:knowlton
ID: 6373736
hotbudare:

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

Thanks,

Tom
0
 
LVL 5

Author Comment

by:knowlton
ID: 6373742
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
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 6373753
You're welcome, glad I could help.  You can also automate this if you are going to be doing this frequently.
0
 
LVL 5

Author Comment

by:knowlton
ID: 6373790
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 5

Author Comment

by:knowlton
ID: 6373812
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
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 6373814
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
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 6373838
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
 
LVL 5

Author Comment

by:knowlton
ID: 6373840
Really?

Cool!
0
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 6373853
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
 
LVL 5

Author Comment

by:knowlton
ID: 6374050
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
 
LVL 6

Accepted Solution

by:
PsychoDazey earned 5 total points
ID: 6374256
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
 
LVL 5

Author Comment

by:knowlton
ID: 6374411
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now