VB and VBA Users Source Code: Enumerate all the queries/stored procs in a Access/SQL Database
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Enumerate all the queries/stored procs in a Access/SQL Database
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Monday, January 08, 2001
Hits:
663
Category:
Database/SQL/ADO
Article:
To enumerate all the queries/stored procedures in an access/sql server database use the following routine (please read the procedure Notes). 'Purpose : Returns all the queries in an Access database 'Inputs : cCN An valid open ADO database connection 'Outputs : A collection of all query names 'Author : Andrew Baker 'Date : 07/07/2000 20:40 'Notes : Must reference MSADOX.DLL ("Microsoft ADO Ext. 2.5") ' FOR SQL Server database use : Select * from sysObjects where type = "p" 'Revisions : Public Function QueryNames(cCN As ADODB.Connection) As Collection Dim oCatalog As New ADOX.Catalog, colQueryNames As New Collection Dim oProc As ADOX.Procedure, oView As ADOX.View On Error GoTo ExitSub Set oCatalog.ActiveConnection = cCN 'Enumerate CrossTab type queries For Each oProc In oCatalog.Procedures colQueryNames.Add oProc.Name Next 'Enumerate Select type queries For Each oView In oCatalog.Views colQueryNames.Add oView.Name Next Set QueryNames = colQueryNames ExitSub: Set oCatalog = Nothing Set oProc = Nothing Set oView = Nothing End Function
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
