How to programmatically retrieve query text of all queries

Question

I would like to programmatically retrieve the query text of every query stored in an access database. I am thinking of using OleDbConnection.GetSchema or OleDbConnection.GetOleDbSchemaTable or even ADOX to get the required information. I am proficient in C#.

Can this be done?

Can you provide an example or link to good examples?

Answer 1

If your intention is to use this in code in the VBA you can try something like this

Dim qdef As QueryDef
Dim qdefs As QueryDefs
Dim i As Integer
Dim name As String
Dim qSql As String

    Set qdefs = Application.CodeDb.QueryDefs
    For Each qdef In qdefs
        qname = qdef.name
        qSql = qdef.SQL
    Next qdef

the qdef object will also give you a lot more info about the query.

for c# you will have to add the ref to the project for the access interop (Microsoft Access ## Object Library)

and use

private void QueryValues()
        {
            Microsoft.Office.Interop.Access.Application app = new Application(); 
            app.OpenCurrentDatabase(@"C:\Tests\C#\MS Access\WindowsApplication1\New Microsoft Office Access 2007 Database.accdb", false,"");
            QueryDefs qdefs = app.CurrentDb().QueryDefs;
            foreach (QueryDef qdef in qdefs)
            {
                string qname = qdef.Name;
                string qSql = qdef.SQL;
            }
            app.Quit(AcQuitOption.acQuitSaveNone);
        }

Answer 2

I had a very similar problem to solve last week, where I wanted to extract view commands from a SQL server. My first idea was to use ADOX, but it eventually could not work with SQL Server (and I did not test it with Access). The theory says that ADOX has a 'views' collection, where each view has a 'command' property. This property holds the SQL string corresponding to the view. Code should then be (after declaring ADOX library in the tools list)

'corrected after @HansUp comments'

Dim cat as ADOX.catalog, _
    vue as ADOX.view

Set cat = New ADOX.catalog
Set cat.activeconnection = myActiveADODBconnection

For each vue in cat.views
    debug.print vue.command.commandText
Next vue

Set vue = nothing
Set cat = nothing