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

>
Want a good read? Try The Summer of His Life (available on Amazon!)

Show Table Structure in Debug Window

 Post Reply Post Reply
Author
Message Reverse Sort Order
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
uploads/84/mod_ShowFields_Duane_Crystal_121115_BAS.zip

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:
mod_ShowFields_Duane_Crystal_121114.bas

    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:


ChkOutIn
==========================
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
        Debug.Print


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,
Crystal
Microsoft MVP
Remote Training & Programming
Let's Connect and Build Together

http://www.AccessMVP.com/strive4peace
http://YouTube.com/LearnAccessByCrystal
~have an awesome day ~
Back to Top
Sponsored Links
>
Want a good read? Try The Summer of His Life (available on Amazon!)

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.