Clever Geek Handbook

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!

+1


source to share


4 answers


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


source


you can combine this with OleDbConnection GetSchema method and also post Remou regarding ADO schemas



oops forgot link: MSDN

+1


source


If you want to execute a quick query manually.

SELECT MSysObjects.Name
FROM MSysObjects
WHERE type = 5
+1


source


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

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

-2


source






More articles:


All Articles