Printing out table design in Access 7.0
John L. Viescas
Jay-
Take a look at:
Tools/Add-Ins/Database Documentor
Unfortunately, you'll need to ask for ALL field properties to see the
Description. If you want only the description, you'll have to rumble
through the TableDef in code. Here's some sample code from my book that
dumps out all tables and queries to get you started. It requires a
pre-defined table that looks like:
TableName (Text) PKey1, Sequence (Number, Long) PKey2, FieldName Text,
Description Text, Type Text, Length Text, and PKey Yes/No.
=======================================================================
Sub DumpTablesAndQueries()
' This routine examines all tables and queries
' It writes rows to a temp table for each field in the table or query
' For tables, it flags which fields are in the Primary Key
' The resulting table is useful as a crossref to see where fields
' are defined and used.
Dim db As Database, tdf As TableDef, fld As Field
Dim idx As Index, qdf As QueryDef, rst As Recordset
Dim strPKeyNames(1 To 10) As String, intPKeyCount As Integer
Dim intI As Integer, varRtn As Variant, intCount As Integer
' Blow past any errors
On Error Resume Next
' Set a variable pointing to the current database
Set db = CurrentDb
' Use the execute method to clean out all rows in the demo table
db.Execute ("Delete * From zTableFields")
' Open a recordset to append new rows only
Set rst = db.OpenRecordset("zTableFields", , dbAppendOnly)
' Be nice, show a temperature bar as we process tables
' Call SysCmd to set up the count
varRtn = SysCmd(acSysCmdInitMeter, "Processing tables...",
db.TableDefs.Count)
' Go through all the tabledefs
For Each tdf In db.TableDefs
' Skip the System and Hidden tables and the demo table
If (tdf.Attributes And (dbSystemObject Or dbHiddenObject)) = 0 And
_
tdf.Name <> "zTableFields" Then
' Set counter for Primary Key fields to zero
intPKeyCount = 0
' Loop through all the indexes looking for the Primary Key
' (It should be named PrimaryKey, but it might not be!)
For Each idx In tdf.Indexes
If idx.Primary Then
' Found the Primary Key - save all the field names for
later
For Each fld In idx.Fields
' We're set up to save only the first 10 fields in
the PKey
If intPKeyCount = 10 Then Exit For
' Increment the counter
intPKeyCount = intPKeyCount + 1
' Save the field name at that counter position
strPKeyNames(intPKeyCount) = fld.Name
Next fld
' This Exit For stops our scan for PKey once we found it
Exit For
End If
Next idx
' Now, examine all the fields and dump them to the demo table
For Each fld In tdf.Fields
' Set up to add a new row
rst.AddNew
' Save table name
rst!TableName = tdf.Name
' Save the field's ordinal position (relative to 0)
rst!Sequence = fld.OrdinalPosition
' Save the field name
rst!FieldName = fld.Name
' Grab the description
' Note: This causes an error when no description has
' been entered because Description is not a Jet property
' Remember, we are bypassing errors above, so this is OK
rst!Description = fld.Properties("Description")
' Scan the list of saved PrimaryKey field names
' to see if this field is in the PKey
For intI = 1 To intPKeyCount
If fld.Name = strPKeyNames(intI) Then
' Found IT!, so mark the PKey column
rst!PKey = True
Exit For
End If
Next intI
' Now, figure out the field type as a text string
Select Case fld.Type
Case dbBoolean
rst!Type = "Yes/No"
rst!Length = 1
Case dbByte
rst!Type = "Byte"
rst!Length = 1
Case dbInteger
rst!Type = "Integer"
rst!Length = 2
Case dbLong
If (fld.Attributes And dbAutoIncrField) Then
rst!Type = "Auto Number"
Else
rst!Type = "Long Integer"
End If
rst!Length = 4
Case dbCurrency
rst!Type = "Currency"
rst!Length = 8
Case dbSingle
rst!Type = "Single"
rst!Length = 4
Case dbDouble
rst!Type = "Double"
rst!Length = 8
Case dbDate
rst!Type = "Date/Time"
rst!Length = 8
Case dbText
rst!Type = "Text"
rst!Length = fld.Size
Case dbLongBinary
rst!Type = "OLE Object"
Case dbMemo
rst!Type = "Memo"
Case dbGUID
rst!Type = "Replication ID"
rst!Length = 16
Case Else
rst!Type = "Unknown"
End Select
' Done -- save the row
rst.Update
' This gets the next field
Next fld
' This End IF matches test for system, hidden tables
End If
' Call SysCmd to update the count
intCount = intCount + 1
varRtn = SysCmd(acSysCmdUpdateMeter, intCount)
' This gets the next tabledef
Next tdf
' Clear the status bar
varRtn = SysCmd(acSysCmdClearStatus)
' Be nice, show a temperature bar as we process queries
' Call SysCmd to set up the count
varRtn = SysCmd(acSysCmdInitMeter, "Processing queries...",
db.QueryDefs.Count)
intCount = 0
' Next, go through all the QueryDefs
For Each qdf In db.QueryDefs
' Examine all the fields and dump them to the demo table
For Each fld In qdf.Fields
' Set up to add a new row
rst.AddNew
' Save table name
rst!TableName = qdf.Name
' Save the field's ordinal position (relative to 0)
rst!Sequence = fld.OrdinalPosition
' Save the field name
rst!FieldName = fld.Name
' Use description to display:
' SourceTable.SourceField
rst!Description = fld.SourceTable & "." & fld.SourceField
' Now, figure out the field type as a text string
Select Case fld.Type
Case dbBoolean
rst!Type = "Yes/No"
rst!Length = 1
Case dbByte
rst!Type = "Byte"
rst!Length = 1
Case dbInteger
rst!Type = "Integer"
rst!Length = 2
Case dbLong
If (fld.Attributes And dbAutoIncrField) Then
rst!Type = "Auto Number"
Else
rst!Type = "Long Integer"
End If
rst!Length = 4
Case dbCurrency
rst!Type = "Currency"
rst!Length = 8
Case dbSingle
rst!Type = "Single"
rst!Length = 4
Case dbDouble
rst!Type = "Double"
rst!Length = 8
Case dbDate
rst!Type = "Date/Time"
rst!Length = 8
Case dbText
rst!Type = "Text"
rst!Length = fld.Size
Case dbLongBinary
rst!Type = "OLE Object"
Case dbMemo
rst!Type = "Memo"
Case dbGUID
rst!Type = "Replication ID"
rst!Length = 16
Case Else
rst!Type = "Unknown"
End Select
' Done -- save the row
rst.Update
' This gets the next field
Next fld
' Call SysCmd to update the count
intCount = intCount + 1
varRtn = SysCmd(acSysCmdUpdateMeter, intCount)
' This gets the next querydef
Next qdf
' Clear the status bar
varRtn = SysCmd(acSysCmdClearStatus)
' Done!
rst.Close
End Sub
--
John Viescas
author, "Running Microsoft Access 97"
http://www.microsoft.com/mspress/books/bio/64.htm
Izjay <iz...@aol.com> wrote in article
<19970303230...@ladder02.news.aol.com>...
> Does anyone know how to print out a table's design page? All I want to
do
> is list all of the fields along with their data types and descriptions
for
> a particular table. When you have a table open in design view, the print
> file menu option is disabled. Also, cutting and pasting does not work.
> I have been trying to look at the field properties using a procedure, but
> I am not having any luck displaying the description for the fields of a
> table.
>
> If anyone can help, please email me.
>
> Jay Blackburn - j...@interzine.com
> Interzine Productions, Inc. - www.interzine.com
>