Roger's Access Library Homepage
Forum Home Forum Home > Other Download Libraries > MS Access MVP Libraries > Long, Crystal
  New Posts New Posts RSS Feed - Show Table Structure in Debug Window
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Please consider donating $1 per sample downloaded, (find out why here)

Show Table Structure in Debug Window

 Post Reply Post Reply
Crystal Long View Drop Down
Microsoft MVP
Microsoft MVP

Joined: 21 May 2012
Location: Colorado
Status: Offline
Points: 35
Post Options Post Options   Thanks (0) Thanks(0)   Quote Crystal Long Quote  Post ReplyReply Direct Link To This Post Topic: Show Table Structure in Debug Window
    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

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 ~
Back to Top
Sponsored Links

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down