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!
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();
}