Retrieving Access Database Schema

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

Guest

I am trying to write my own program (from within an Access form) that will
print out a simple listing of all the objects in an Access database. I am
using the .OpenSchema(adSchemaTables) feature. However, I am discovering
that if a query is anything but a SELECT query (the TABLE_TYPE is "VIEW" for
these kinds of queries), that the RecordSet returned from the OpenSchema does
not show me those other types of queries. For example, I have an .MDB with
35 queries, but the RecordSet only shows me 5 of those queries. What am I
doing wrong?

- Thanks,

JRD
 
Hi John,

It looks like all you want to do is print out a nice list of all the database objects, correct? If
so, how about trying 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!
 
Thanks for the reference - I may use that, but am also hoping to get some
code going for myself so I can do some more custom things with it.

- JRD
 
John,

Here are some sample sql statements that I use to find all objects in an mdb.

SQLForms = "SELECT MSysObjects.Name FROM MsysObjects " & _
"WHERE (Left$([Name],1)<>""~"") " & _
"And (MSysObjects.Type)=-32768 ORDER BY MSysObjects.Name;"

SQLTables = "SELECT MSysObjects.Name FROM MsysObjects " & _
"WHERE (Left$([Name],1)<>""~"") " & _
"And (Left$([Name],4)<>""Msys"") " & _
"And (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;"

SQLQuery = "SELECT MSysObjects.Name FROM MsysObjects " & _
"WHERE (Left$([Name],1)<>""~"") " & _
"And (MSysObjects.Type)=5 ORDER BY MSysObjects.Name;"

SQLReport = "SELECT MSysObjects.Name FROM MsysObjects " & _
"WHERE (Left$([Name],1)<>""~"") " & _
"And (MSysObjects.Type)=-32764 ORDER BY MSysObjects.Name;"

SQLModule = "SELECT MSysObjects.Name FROM MsysObjects " & _
"WHERE (Left$([Name],1)<>""~"") " & _
"AND (MSysObjects.Type)= -32761 ORDER BY MSysObjects.Name;"

SQLMacro = "SELECT MSysObjects.Name FROM MsysObjects " & _
"WHERE (Left$([Name],1)<>""~"") " & _
"AND (MSysObjects.Type)= -32766 ORDER BY MSysObjects.Name;"


Just some VBA code

lwells
 
Back
Top