A Microsoft Office Access database contains Tables, Queries, Forms and Reports.
Is it possible to build and save a query in the Access database from C#?
For example, I know I can use OLEDB to connect to an Access database and use SQL commands using its tables and already defined queries, but how would I build a new query and then save it in the database?
If you want to add Querydefinitons to an existing access database you can do so with the Access Interop asssembly.
Create a new c# project and add a reference to:
Microsoft Office 12.0 Access database engine Object Library
(or a version that matches your Office/Access version)
This code creates a Query in the Access Database for every table in the database to query the count of rows:
var dbe = new DBEngine();
var db = dbe.OpenDatabase(@"c:\path\to\your\youraccessdatabase.accdb");
// loop over tables
foreach (TableDef t in db.TableDefs)
{
// create a querydef
var qd = new QueryDef();
qd.Name = String.Format("Count for {0}", t.Name);
qd.SQL = String.Format("SELECT count(*) FROM {0}", t.Name);
//append the querydef (it will be parsed!)
// might throw if sql is incorrect
db.QueryDefs.Append(qd);
}
db.Close();
