Cannot -Save As Table- using Access Documenter

[Dan Harmon]

I am trying to create an object definition table much like I used to do
frequently in Access 97. Currently I am using Access 2000 and don't have
the menu option to Save As Table after I use the Tools-Analyze-Documenter
menu option to create a report of database objects. Has this feature been
dropped from newer versions (post Access 97) ? Can I save the data from the
report in the Documenter into a table in the current database using Access
2000?

Thanks for any insight you can provide.

 

[Jeff Conrad]

Hi Dan,

If you would like just like a simple report of all the database objects I would suggest you try my
new "Doug Steele Object Documentor" Add-In for Access. This add-in will create a nice report of all
your database objects for easy reference. The report will list all the objects by category in
alphabetical order in a three-column layout. It looks very sweet in my opinion and it's even easier
to use than the Access Documentor. Plus, you get a nice report saved in your database that you can
open again any time you wish! There is one version for Access 97 and another version that works for
2000, 2002, and 2003.

After installing, which takes all of about a minute to do, you simply do:
Tools | Add-Ins | Doug Steele Object Documentor
That's it! Poof! A nice slick report created in your database in just a couple of seconds.

You can find the free Add-In on MVP Doug Steele's site here:

http://members.rogers.com/douglas.j.steele/Documentor.html

Hope you like it!

I have also created another Access Add-In called the "John Viescas Table Documentor" that will
create a nice report of all your tables and fields. In a nut shell the Add-In creates a new key
table in your database, loops through each table (including linked ones) grabbing the information
needed, writes all this information to the key table, creates the report entirely in code, saves it,
and then displays it on the screen for you. That report can be opened again at any time since it is
saved as a new report object in your database. Easy as pie!

I am awaiting word back from John about the possibility of having him post it on his web site where
anyone can download it. In the meantime I do not believe he would have a problem if I sent you a
copy. If you do 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) and which version you need. There is one version for
Access 97 and one for 2000 which works with 2000, 2002, and 2003.

 

[Dan Harmon]

Thanks Jeff,

You're right, that is sweet and simple to use. However it doesn't
accomplish my ultimate goal. I'm trying to create a list of query names as
the list source for a drop down box. Ultimately I'd like it to be fully
dynamic and populate as the form opens. I was hoping to store the data in a
table that I can write SQL against.

 

[Douglas J. Steele]

No need to store it yourself: it's already in the MSysObjects table.

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "~sq*") AND ((MSysObjects.Type)=5))
ORDER BY MSysObjects.Name;

 

[Jeff Conrad]

Hi Dan,

Glad you liked the Add-In.
As Doug already mentioned, there is no need to save a list of your queries in a table; just query
the System Tables. Doug already provided the SQL for queries, but here is some SQL to get a list of
the other database objects. Just base a combo box on these SQL statements.

--For Queries use:
SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((MsysObjects.Type)=5))
ORDER BY MsysObjects.Name;

--For Forms use:
SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((MsysObjects.Type)=-32768))
ORDER BY MsysObjects.Name;

--For Reports use:
SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((MsysObjects.Type)=-32764))
ORDER BY MsysObjects.Name;

--For Macros use:
SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((MsysObjects.Type)=-32766))
ORDER BY MsysObjects.Name;

--For Tables use:
SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type)=1))
ORDER BY MsysObjects.Name;

--For Modules use:
SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((MsysObjects.Type)=-32761))
ORDER BY MsysObjects.Name;

Hope that helps.

 

[Dan Harmon]

Hi Dan,

Glad you liked the Add-In.
As Doug already mentioned, there is no need to save a list of your queries in a table; just query
the System Tables. Doug already provided the SQL for queries, but here is some SQL to get a list of
the other database objects. Just base a combo box on these SQL statements.

--For Queries use:
SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((MsysObjects.Type)=5))
ORDER BY MsysObjects.Name;

--For Forms use:
SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((MsysObjects.Type)=-32768))
ORDER BY MsysObjects.Name;

--For Reports use:
SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((MsysObjects.Type)=-32764))
ORDER BY MsysObjects.Name;

--For Macros use:
SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((MsysObjects.Type)=-32766))
ORDER BY MsysObjects.Name;

--For Tables use:
SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type)=1))
ORDER BY MsysObjects.Name;

--For Modules use:
SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((MsysObjects.Type)=-32761))
ORDER BY MsysObjects.Name;

Hope that helps.
--
Jeff Conrad
Access Junkie
Bend, Oregon

Thanks Jeff,

You're right, that is sweet and simple to use. However it doesn't
accomplish my ultimate goal. I'm trying to create a list of query names as
the list source for a drop down box. Ultimately I'd like it to be fully
dynamic and populate as the form opens. I was hoping to store the data in a
table that I can write SQL against.

objects I
would suggest you try my will
create a nice report of all in
your database that you can a
couple of seconds. Table
Documentor" that will ones)
grabbing the information report
entirely in code, saves it, post
it on his web site where a
problem if I sent you a need.
There is one version for feature
been from
the

 

[Duane Hookom]

The data created in the documenter is always saved until the next time you
run it. The information is saved in a table that you can link to. The
link/connection on my PC is "DATABASE=C:\Documents and Settings\Duane
Hookom\Application Data\Microsoft\Access\ACWZUSRT.MDT;TABLE=doc_tblObjects".
This table is much the same as the Access 97 table. To get the tables,
fields, etc, create a query with this sql:
SELECT doc_tblObjects.Name AS TableName,
doc_tblObjects_1.Name AS FieldName,
doc_tblObjects_1.Extra2 AS FieldType,
doc_tblObjects_1.Extra3 AS FieldSize
FROM doc_tblObjects AS doc_tblObjects_1
INNER JOIN doc_tblObjects
ON doc_tblObjects_1.ParentID = doc_tblObjects.ID
WHERE (((doc_tblObjects_1.TypeID)=11));