以编程方式构建访问查询 [英] Programmatically Build Access Query

问题描述

Microsoft Office Access数据库包含表，查询，表单和报表.

A Microsoft Office Access database contains Tables, Queries, Forms and Reports.

是否可以从C#构建查询并将其保存在Access数据库中?

Is it possible to build and save a query in the Access database from C#?

例如，我知道我可以使用OLEDB连接到Access数据库并使用其表和已定义的查询使用SQL命令，但是我将如何构建一个新查询并将其保存在数据库中?

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?

推荐答案

如果要将Querydefinitons添加到现有的Access数据库中，则可以使用Access Interop组件来完成.

If you want to add Querydefinitons to an existing access database you can do so with the Access Interop asssembly.

创建一个新的c#项目并添加对以下内容的引用:

Create a new c# project and add a reference to:

Microsoft Office 12.0 Access数据库引擎对象库

Microsoft Office 12.0 Access database engine Object Library

(或与您的Office/Access版本匹配的版本)

(or a version that matches your Office/Access version)

此代码在Access数据库中为数据库中的每个表创建一个查询，以查询行数:

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

这篇关于以编程方式构建访问查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！