Visual Basic Programming Code Examples

Visual Basic > Database SQL Stuff Code Examples

Enumerate all the queries-stored procs in a Access-SQL Database



Enumerate all the queries-stored procs in a Access-SQL Database

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
'Notes     :    Must reference MSADOX.DLL ("Microsoft ADO Ext. 2.5")
'               FOR SQL Server database use : Select * from sysObjects where type = "p"


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