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:AutoGenerate11 Jet OLEDB:One BLOB per Page12 Jet OLEDB:Compressed UNICODE Strings13 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();