report of table fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My request is a simple on, I hope, at least for the info on a report. I
would like to be able to have a report that will print out the table name and
the fields located in the table. Can that be done? If so, how? I know
about the documenter but all I'm looking for is the table name and the fields
in the table.
Thanks in advance for any assistance given.
*** John
 
Report: I would like to be able to have
a report that will print out the table name and
the fields located in the table. Can that be done?
If so, how? I know about the documenter
but all I'm looking for is the table name and
the fields in the table.
Click to expand...

Access Help on the TableDefs Collection and the Field Collection of each
Table is your friend.

Here's code to Debug.Print the Table and Field names when you run the
function from the Immediate Window (Access 2002, but this same code should
run in Access 97, 2000, or 2003, too).

Function ListTables() As Long
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fld As DAO.Field
Dim lngNoTables As Long

Set db = CurrentDb()
For Each td In db.TableDefs
lngNoTables = lngNoTables + 1
If Left(td.Name, 4) <> "MSys" Then
Debug.Print td.Name
For Each fld In td.Fields
Debug.Print " " & fld.Name
Next
End If
Next
ListTables = lngNoTables
End Function

If you want a nice report, you can put the information into a Table and
create the report on that Table. If it's just for my own use, and there's
not too much data, I usually just copy and paste the Debug.Printed output
into a Notepad, Wordpad, or Microsoft Word file.

There's an alternative method, using the officially undocumented System
Tables, but undocumented "things" could change with any maintenance release
or new version (though things like Table Defs and Fields are unlikely to),
so this is "safer".

Larry Linson
Microsoft Access MVP
 
in message:
My request is a simple on, I hope, at least for the info on a report. I
would like to be able to have a report that will print out the table name and
the fields located in the table. Can that be done? If so, how? I know
about the documenter but all I'm looking for is the table name and the fields
in the table.
Thanks in advance for any assistance given.
*** John
Click to expand...

Here are a few options for you:

1. You can use the built-in Access Documentor
(Tools | Analyze | Documentor) to get a huge list of just about everything
you ever wanted to know about the database. At the same time, you can
keep the logging and paper manufacturing industry going strong in Oregon!
Depending upon the options you select, you could fell several trees.
:-)

2. I created an Access Add-In that does exactly this. It is even easier to use
than the built-in Access Documentor and creates a slick report that will list
each table (including linked ones) and their field properties in an easy-to-read
format. I recently finished Version 2.0 of the add-in. This version even works
on MDE files.

Once installed (takes less than a minute) all you have to do is launch it from the
Add-ins menu. After a few seconds a slick report is displayed on the screen.
Easy as pie!

The report will list each Field Name, Description, Type, Length, and which
field(s) compose the Primary Key.

The display output will be something like this:

Table: tblVendors
Field Name: Description Type Length Primary
VendorID ID Field Autonumber 4 X
VendorName Vendor's Name Text 50
etc...

Will this work for you?

If you wish to have a copy, please let me know where you would like it sent to
(please mung the e-mail address so you won't get spammed!). There is one
version for Access 97 and one for 2000 which works with Access 2000, 2002,
and 2003. I will need to know which version you need.

3. David Hare-Scott has also written a simple table documentor. You can
download it here:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hare-Scott,David

4. Armen Stein has something here as well:
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp
(Look for Data Dictionary Report Utility)

5. MVP Allen Browne has some TableInfo code here:
http://allenbrowne.com/func-06.html

6. FMS has utilities that can do this as well:
http://www.fmsinc.com/

Hope that gives you some ideas,
 
Jeff, thanks for the info. I'm aware of the documentor and no doubt the
logging and paper industry know me well. I will look at those you mentioned.
I am trying to keep it as simple as possible. I would like to see what you
did. My email address is (e-mail address removed). Just remove the no
spam. And, again, thanks.
*** John
 
in message:
Jeff, thanks for the info. I'm aware of the documentor and no doubt the
logging and paper industry know me well. I will look at those you mentioned.
I am trying to keep it as simple as possible. I would like to see what you
did. My email address is <snipped> Just remove the no
spam. And, again, thanks.
*** John
Click to expand...

No problem John.
I'll send along a copy in a little bit.
 
Back
Top