Print Page | Close Window

Show Table Structure in Debug Window

Printed From: Roger's Access Library
Category: Other Download Libraries
Forum Name: Long, Crystal
Forum Description: Access Basics is designed for those of you with a thirst to understand the fundamentals of Access with programming in mind ... whether you realize it or not.
Printed Date: 14 Nov 2018 at 10:48pm

Topic: Show Table Structure in Debug Window
Posted By: Crystal Long
Subject: Show Table Structure in Debug Window
Date Posted: 14 Nov 2012 at 10:40pm
Show Fieldnames, Data Types, Sizes, and Descriptions for Tables

this code was originally written by Duane Hookom, then modified by me to show additional information

BAS module - uploads/84/

to import a module into your database, UNZIP the attached file it to pull out the BAS file. BAS is a VBA (Visual Basic for Applications) standard (general) module.

then open the database you want to document, press Alt-F11 to go to a Visual Basic window.  From the menu, choose: File, Import

Navigate to:

    NEEDS reference to
    Microsoft DAO Object Library -OR- Microsoft Office ##.0 Access Database Engine Object Library
    (from the menu: Tools, References...)

    Debug, Compile and then Save

then run the procedure at the top called RunShowFieldsForAllTables

if you have too many lines for the debug window, you can document tables one at a time by changing and using RunShowFieldsForTable

here is an example of the output:

0  chkID, 4 (Lng), 4, chkID (Auto)
1  usrID, 4 (Lng), 4, User
2  FileID, 4 (Lng), 4, File
3  dtmOut, 8 (Date), 8, date/time checked out
4  dtmIn, 10 (Txt), 50, date/time checked in
5  chkNote, 10 (Txt), 50, note about this record
6  IDadd, 4 (Lng), 4, User who added record
7  IDedit, 4 (Lng), 4, User who edited record
8  dtmAdd, 8 (Date), 8, Date/Time record was added
9  dtmEdit, 8 (Date), 8, Date/Time record was edited

here is the relevant code that prints this information:

    Debug.Print tbl.Name
    Debug.Print "=========================="
    ' ...
        Debug.Print Fld.OrdinalPosition & "  " & Fld.Name _
         & ", " & Fld.Type & " (" & GetDataType(Fld.Type, True) & ")" _
         & ", " & Fld.Size;
        Debug.Print ", " + Fld.Properties("Description");
        If (Fld.Attributes And 16) = 16 Then
           Debug.Print " (Auto)";
        End If

press Ctrl-G to Goto the debuG (Immediate) window when you are in a the VBE (Visual Basic Editor).  Drag it by the titlebar to move it.

I like to turn the debug window on before I run the code

... and now I see I should have added PK field indicators too ... gotta stop somewhere ;)

Warm Regards,
Microsoft MVP
Remote Training & Programming
Let's Connect and Build Together
~have an awesome day ~

Print Page | Close Window