C# 创建和连接Access数据库文件

连接Access07

1.Access07引用COM组件“Microsoft ADO Ext.6.0 for DDL and Security”, 

2.引用ADOX命名空间

using ADOX;
using System.Data.OleDb;

3.string strConnect = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source = "
                + "C:\\Users\\Administrator\\Desktop\\test1.accdb;"
                + "Jet OLEDB:Database Password=cpls"; //若有密码

4.string Dbname = "C:/Users/Administrator/Desktop/CreateAccess.accdb";
            CatalogClass access = new CatalogClass();
            access.Create("Provider =Microsoft.ACE.OLEDB.12.0;Data Source =" + Dbname + ";");

5.

连接03

引用Microsoft ADO Ext.2.8 for DDL and Security，语句中将ACE改为Jet，12.0改成4.0

 ADOX列举的属性有：
0 Autoincrement 自动编号
1 Default 默认值
2 Description 
3 Nullable 必填字段
4 Fixed Length 
5 Seed 
6 Increment 
7 Jet OLEDB:Column Validation Text 有效性文本
8 Jet OLEDB:Column Validation Rule 有效性规则
9 Jet OLEDB:IISAM Not Last Column 
10 Jet OLEDB:AutoGenerate
11 Jet OLEDB:One BLOB per Page
12 Jet OLEDB:Compressed UNICODE Strings
13 Jet OLEDB:Allow Zero Length 允许空字符串
14 Jet OLEDB:Hyperlink 超链接型

 example

            try
            {
                string Dbname = "C:/Users/Administrator/Desktop/CreateAccess.accdb";
                ADOX.CatalogClass access = new ADOX.CatalogClass();
                if (!File.Exists(Dbname))
                {
                    access.Create("Provider =Microsoft.ACE.OLEDB.12.0;Data Source =" + Dbname + ";");
                    ADOX.TableClass table = new TableClass();
                    table.ParentCatalog = access;
                    table.Name = "CreateTable";
                    ColumnClass col = new ColumnClass();
                    col.ParentCatalog = access;
                    col.Type = ADOX.DataTypeEnum.adInteger;
                    col.Name = "ID";
                    col.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
                    col.Properties["AutoIncrement"].Value = true;
                    table.Columns.Append(col, DataTypeEnum.adInteger, 0);
                    access.Tables.Append(table);
                }
                else
                {
                    Console.WriteLine("已存在同名文件！");
                }
            }
            catch (Exception ex)
            { Console.WriteLine(ex.Message); }
            finally
            { 
            
            }

 //////

创建表（1）
            string strConnect = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source = "
               + "C:\\Users\\Administrator\\Desktop\\test1.accdb;"
               + "Jet OLEDB:Database Password=cpls";
            OleDbConnection con = new OleDbConnection(strConnect);
            OleDbCommand cmd = con.CreateCommand();
            cmd.CommandText = "CREATE TABLE table1(ID AUTOINCREMENT,GdNum TEXT(50),Address TEXT(50),PrintResult yesno,PrintDate DateTime,CONSTRAINT table1_PK PRIMARY KEY(ID))";
            con.Open();
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            con.Close();

 创建表（2）使用ADODB,添加引用COM中的Microsoft ActiveX Data Objects 6 Library
            
            CatalogClass cat = new CatalogClass();
            string strConnect = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source = "
               + "C:\\Users\\Administrator\\Desktop\\test1.accdb;"
               + "Jet OLEDB:Database Password=cpls";
            ADODB.Connection con = new ADODB.Connection();
            con.Open(strConnect, null, null, -1);
            cat.ActiveConnection = con;
            ADOX.TableClass table2 = new TableClass();
            table2.ParentCatalog = cat;
            table2.Name = "CreateTable";
            ColumnClass col = new ColumnClass();
            col.ParentCatalog = cat;
            col.Type = ADOX.DataTypeEnum.adInteger;
            col.Name = "ID";
            col.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
            col.Properties["AutoIncrement"].Value = true;
            table2.Columns.Append(col, ADOX.DataTypeEnum.adInteger, 0);
            table2.Keys.Append("FirstTablePrimaryKey", KeyTypeEnum.adKeyPrimary, col, null, null);
            table2.Columns.Append("CustomerName", ADOX.DataTypeEnum.adVarWChar, 50);
            table2.Columns.Append("Age", ADOX.DataTypeEnum.adInteger, 9);
            table2.Columns.Append("Birthday", ADOX.DataTypeEnum.adDate, 0);
            cat.Tables.Append(table2); 

 

///Access操作
            
            string strConnect = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source = "
               + "C:\\Users\\Administrator\\Desktop\\test1.accdb;"
               + "Jet OLEDB:Database Password=cpls";
            OleDbConnection con = new OleDbConnection(strConnect);
            OleDbCommand cmd = con.CreateCommand();
            //cmd.CommandText = "DROP TABLE CreateTable ";       //drop table
            //cmd.CommandText = "INSERT INTO CreateTable (ID,CustomerName,Age,Birthday) Values /(1,'Ashley',22,#1988/01/03#)";//insert into access时间操作加##括起来
            //cmd.CommandText = "UPDATE CreateTable SET Birthday=#1991/07/04# WHERE ID=1 ";//update
            //cmd.CommandText = "DELETE FROM CreateTable WHERE ID=1";//delete
            //cmd.CommandText = "DELETE * FROM CreateTable";//清除表格内容
            //cmd.CommandText = "SELECT COUNT(*) FROM CreateTable";//
            //cmd.CommandText = "SELECT Birthday FROM CreateTable WHERE ID=2";//
            //cmd.CommandText = "ALTER TABLE CreateTable ADD COLUMN Marital_status yesno ";//添加行
            //cmd.CommandText = "ALTER TABLE CreateTable DROP COLUMN Marital_status yesno ";//删除行
            cmd.CommandText = "SELECT * INTO table2 FROM CreateTable";//Copy
            con.Open();
            OleDbDataReader oledr = cmd.ExecuteReader();
            while (oledr.Read())
            {
                Console.WriteLine(oledr[0]);
            }
            oledr.Close();
            cmd.Dispose();
            con.Close();