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?
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);
}
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
I was successful with this code:
Public Sub DebugQuerySQL()
Dim q As Object ' QueryDefs
For Each q In CurrentDb.QueryDefs
Debug.Print q.Name ' Debug query name
Debug.Print q.SQL ' Debug query SQL
Next
End Sub
