tool to show all sql from Access DB (ADO)?

Does anyone have vbscript to report all the Query names and full sql
statements in an Access DB? I'm not particularly interested in any report
related sql just for those found in the query window. I have many Access
databases and sometimes need to document what they contain or to look for
queries using certain tables. Access also can contain stored procedures. Is
it possible to read this sql also? Even though the database may contain some
it doesn't appear to be visible anywhere.

thanks
LJB

Viatcheslav
Thu Apr 29 10:54:17 CDT 2004

You can obtain list of tables, views, procedures and SQL for views and
procedures. Use OpenSchema method from ADO Connection object and examine
schemas adSchemaTables (=20), adSchemaViews (=23) and adSchemaProcedures
(=16). SQL for views in VIEW_DEFINITION field of adSchemaViews, for
procedures - in PROCEDURE_DEFINITION field of adSchemaProcedures. SQL for
tables is "SELECT * FROM [TableName]" (replace TableName with real table
name).

//------------------------------------
Regards,
Vassiliev V. V.
http://www-sharp.com -
Scripting/HTA/.Net Framework IDE

"ljb" <.> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
news:eLHke1fLEHA.1612@TK2MSFTNGP12.phx.gbl...
> Does anyone have vbscript to report all the Query names and full sql
> statements in an Access DB? I'm not particularly interested in any report
> related sql just for those found in the query window. I have many Access
> databases and sometimes need to document what they contain or to look for
> queries using certain tables. Access also can contain stored procedures.
Is
> it possible to read this sql also? Even though the database may contain
some
> it doesn't appear to be visible anywhere.
>
> thanks
> LJB
>
>

ljb
Thu Apr 29 11:45:26 CDT 2004


"Viatcheslav V. Vassiliev" <msnewsgroup@www-sharp.com> wrote in message
news:ubAf9IgLEHA.2736@TK2MSFTNGP11.phx.gbl...
> You can obtain list of tables, views, procedures and SQL for views and
> procedures. Use OpenSchema method from ADO Connection object and examine
> schemas adSchemaTables (=20), adSchemaViews (=23) and adSchemaProcedures
> (=16). SQL for views in VIEW_DEFINITION field of adSchemaViews, for
> procedures - in PROCEDURE_DEFINITION field of adSchemaProcedures. SQL for
> tables is "SELECT * FROM [TableName]" (replace TableName with real table
> name).

Indeed it does. Knowing what to look for I found this link
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthopenschemax.asp

thanks
LJB

v-kevy
Fri Apr 30 02:37:26 CDT 2004

Hi LJB,

It as nice to hear that you have had the problem resolved. Thanks for
sharing your experience with all the people here. If you have any
questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."