How do I list all the queries in a MS Access file using OleDB in C#?

Question

I have an Access 2003 file that contains 200 queries, and I want to print out their representation in SQL. I can use Design View to look at each query and cut and paste it to a file, but that's tedious. Also, I may have to do this again on other Access files, so I definitely want to write a program to do it.

Where are queries stored an Access db? I can't find anything saying how to get at them. I'm unfamiliar with Access, so I'd appreciate any pointers. Thanks!

Answer 1

Not in C#, but may be a good place to start:

http://www.datastrat.com/Code/DocDatabase.txt

Answer 2

I cannot help with c#, but with DAO and VBA you have:

For Each qdf In CurrentDB.QueryDefs
    '~* are system queries for data for forms, controls etc'
    If Left(qdf.Name,1) <>"~" Then
        s = qdf.SQL
    End If
Next

You can also look at the system tables MsysObjects and MsysQueries, but that might be a little tedious.

Then there are ADO schemas, for example, this would list select queries:

Sub ListQueriesAdo()
Dim rs As ADODB.Recordset
    Set rs = CurrentProject.Connection.OpenSchema( _
        adSchemaViews, Array(Empty, Empty, Empty))
     For i = 0 To rs.Fields.Count - 1
         Debug.Print rs.Fields(i).name
     Next

     Do While Not rs.EOF
         Debug.Print rs!VIEW_DEFINITION
         rs.MoveNext
     Loop
    rs.Close
    Set rs = Nothing
End Sub

And you can use adSchemaProcedures to show action queries.

Finally, you can use a catalog:

Private Sub ListQueriesCat()
Dim cn As ADODB.Connection
Dim mcat As ADOX.Catalog
Dim mview As ADOX.View
Dim cmd As ADODB.Command

    Set cn = CurrentProject.Connection

    Set mcat = New ADOX.Catalog
    Set mcat.ActiveConnection = cn

    For Each mview In mcat.Views
        Debug.Print mview.Command.CommandText
    Next

    Set mview = Nothing
    Set cmd = Nothing
    Set mcat = Nothing
    cn.Close
End Sub

Answer 3

you can put this together using the OleDbConnection's GetSchema method along with what Remou posted with regards to the ADO Schemas

oops forgot link: MSDN

Answer 4

Procedures are what you're looking for:

OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();

DataTable queries = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures, null);

conn.Close();

This will give you a DataTable with the following columns in it (among others):

PROCEDURE_NAME: Name of the query

PROCEDURE_DEFINITION: SQL definition

So you can loop through the table like so:

foreach(DataRow row in queries.Rows)
{
    // Do what you want with the values here
    queryName = row["PROCEDURE_NAME"].ToString();
    sql = row["PROCEDURE_DEFINITION"].ToString();
}