C# access、excel、sql和压缩access

 1 using System;
 2 using System.Data;
 3 using System.Data.OleDb ;
 4 using System.Linq;
 5 using System.IO;
 6 
 7 OleDbConnection conn=new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\\123.mdb");
 8 
 9 OleDbCommand comm=new OleDbCommand();
10 
11 comm.Connection=conn;
12 
13 // insert A from B
14 
15 // comm.CommandText=string.Format("INSERT INTO {0} select * from  [{1}].{0} where
16 
17 // TIName=‘{2}‘",tableNames[j],sourceDbPath,tiNames[i]);
18 
19 comm.CommandText="insert into userName select * from [D:\\456.mdb].userName";
20 
21 // insert data from access to excel
22 
23 // comm.CommandText = "select * into [Excel 8.0;database=E:\\123.xls].Sheet1 from [E:\\123.mdb].GlobalParameter";
24 
25 comm.ExecuteNonQuery();
26 
27 comm.Dispose();
28 conn.Close();
29 
30 // 压缩数据库（网上资料）
31 
32 //创建 Jet 引擎对象
33 object objJetEngine = Activator.CreateInstance(Type.GetTypeFromProgID("JRO.JetEngine"));
34 
35 //设置参数数组
36 //根据你所使用的Access版本修改 "Jet OLEDB:Engine Type=5" 中的数字.
37 //5 对应 JET4X 格式 (access 2000,2002)
38 
39 object[] objParams = new object[] {
40 String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}",DbPath), //输入连接字符串
41 String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Engine Type=5",tempDbPath) //输出连接字符串
42 };
43 
44 //通过反射调用 CompactDatabase 方法
45 try {
46 objJetEngine.GetType().InvokeMember("CompactDatabase",
47 System.Reflection.BindingFlags.InvokeMethod,
48 null,
49 objJetEngine,
50 objParams);
51 
52 } catch (Exception) {
53 Console.WriteLine("Fail to compact database:\r\n Can‘t invoke compact command!");
54 Console.ReadKey(true);
55 throw;
56 }
57 //删除原数据库文件 
58 File.Delete(DbPath);
59 //重命名压缩后的数据库文件
60 File.Move(tempDbPath, DbPath);
61 //释放Com组件
62 System.Runtime.InteropServices.Marshal.ReleaseComObject(objJetEngine);
63 objJetEngine = null;