Solved

PRINT /  EXPORT the Design View of a Table

Posted on 2001-08-10
15
299 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

863 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

20 Experts available now in Live!

Get 1:1 Help Now