How can I list all requests in an MS Access file using OleDB in C #?
I have an Access 2003 file that contains 200 queries and I want to print their SQL representation. I can use the Design View to view each request and cut and paste it into the file, but this is tedious. Also, I might have to do it again in other access files, so I definitely want to write a program to do this.
Where are db Access queries stored? I can't seem to find anything talking about how to get to them. I am unfamiliar with Access, so I would appreciate any pointers. Thank!
Procedures are what you are 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: Request name
PROCEDURE_DEFINITION: SQL definition
So, you can loop through the table like this:
foreach(DataRow row in queries.Rows)
{
// Do what you want with the values here
queryName = row["PROCEDURE_NAME"].ToString();
sql = row["PROCEDURE_DEFINITION"].ToString();
}
you can combine this with OleDbConnection GetSchema method and also post Remou regarding ADO schemas
oops forgot link: MSDN
If you want to execute a quick query manually.
SELECT MSysObjects.Name
FROM MSysObjects
WHERE type = 5
Not in C #, but might be a good place to start:
http://www.datastrat.com/Code/DocDatabase.txt