About Geoff Expertise I specialise in database analysis and design, SQL and database queries using QBE and VBA. In my work, I use MS Access together with MS SQL Server as ETL (Extraction - Transformation - Loading) tools for migrating data between business ERP systems and data stores. My forte is building bespoke functions and applications.
See my
website for example apps and downloads
Experience I am a chartered engineer with 30 years of engineering and business experience, member of the BCS and have been working specifically in database applications, including SQL Server (v7/8/2000) for the last 9 or so years. I previously taught a course in Database Analysis and Design, but am now a freelance consultant and systems analyst.
Commercial database design and development work undertaken.
Using MS Access - Access 2003: how to print table design info
Expert: Geoff - 2/16/2005
Question Is there a way in Access 2003 to print what I visually can see on the screen in Table, Design Mode? Meaning, I'd like to have a printout that includes the fieldnames, data types, and descriptions. I know the documentor feature can print these items, but they come along with a bunch of other stuff I don't need (unless there is a way I don't know about to only print these 3 items I'd like). And if there is not a way to do this directly in Access, can it be done via Excel somehow (via a macro, perhaps)? Thanks very much for your time!
Answer Hi Sheree,
I did something on this a few years ago, but couldn't find it, so I built a new module...
You will need to ensure that there is a refeence to DAO (Menu / tools / references - select MS DAO...)
This module goes through all tables in the DB and generates a report, C:\TableDefinitions.txt
Here's the code - place this in a module, then run it...
'...............................................................
' Procedure for printing out Access Database Table Definitions
'
' By Geoff Johns GTS - gmjohns@fastmail.fm - Copyright 2005
'
' You may use this software freely provided this header is
' retained in your code. The Developer carries absolutely no
' liabiliy for any consequences whatsoever.
'...............................................................
Dim tdf As DAO.TableDef, db As DAO.Database
Dim fld As DAO.Field, prp As DAO.Property
Dim DataType(30) As String, sDescrip As String
Set db = CurrentDb
Set prp = db.CreateProperty("Description", dbText)
'Open the reporting file
Open "c:\TableDefinitions.txt" For Output As #1
For Each tdf In db.TableDefs
Write #1, "Table: " & tdf.Name
'Debug.Print tdf.Name
For Each fld In tdf.Fields
sDescrip = ""
For Each prp In fld.Properties
If prp.Name = "Description" Then
sDescrip = prp.Value
End If
Next
Write #1, Tab(5); fld.Name, Tab(30); DataType(fld.Type), Tab(45); sDescrip
'Debug.Print , fld.Name, DataType(fld.Type), sDescrip
Next
'Debug.Print " "
Write #1,
Next
