Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

PRINT /  EXPORT the Design View of a Table

Posted on 2001-08-10
15
Medium Priority
?
319 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:Tom 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 5

Author Comment

by:Tom 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:Tom 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:Tom 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
 
LVL 5

Author Comment

by:Tom 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:Tom 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:Tom 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 20 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:Tom 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

580 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