连接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:On
e 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();