vb控制ACCESS及EXCEL.doc

VB控制EXCEL及ACCESS 局域网Access数据库用ADO连接 cnnAccess.Open provider=Microsoft.jet.OLEDB.4.0;data source=数据库所在机器ID数据库文件夹db1.mdb;Jet OLEDB:Database Password=123 一、概述 使用ACCESS存取数据肯定比EXCEL快，数据量越大越明显（本程序当数据量突破1000时，感觉比较明显）。 二、VB控制EXCEL 1、“创建实例法”： 、定义： Public Ex As Object Public Exwbook As Object Public Exsheet As Object 、打开与关闭： Set Ex = CreateObject(Excel.Application) Ex.Visible = False Set Exwbook = Nothing Set Exsheet = Nothing Ex.DisplayAlerts = False 默认回答 Set Exwbook = Ex.Workbooks.Open(P_csszqwjm) 打开P_csszqwjm文件 Set Exsheet = Exwbook.Sheets(P_cssz) 设置当前工作表为P_cssz Exwbook.Close Ex.DisplayAlerts = True Ex.Quit Set Exsheet = Nothing Set Exwbook = Nothing Set Ex = Nothing 、EXCEL控制命令： 在VB中控制EXCEL只需在VBA语句前加上相应的对象名(或将原来的workbooks、sheet替换为自己设置的变量Exwbook、Exsheet)即可。如： Exwbook.sheet.add 增加工作表 Exsheet.cells(1,1)=”试验” 2、使用ADO方法： (具体参见下一部分) Public Adocon As ADODB.Connection 链接 Public Adorst As ADODB.Recordset 记录集 Adocon.Open Provider=Microsoft.Jet.OLEDB.4.0; & _ Data Source=C:Book1.xls; & Extended Properties=Excel 8.0; Adorst.Open Select * from Sheet1$, Adocon, adOpenStatic 打开Sheet1，也可以是定义的名称，或一个Range。 三、VB(VBA)控制ACCESS VB(VBA)控制ACCESS有几种方法，这里只讨论ADO。 1、定义： Public Adocon As ADODB.Connection 键接 Public Adorst As ADODB.Recordset 记录集 2、打开与关闭： Set Adocon = New ADODB.Connection Set Adorst = New ADODB.Recordset Adocon.Open Provider=Microsoft.Jet.OLEDB.4.0;Data Source= & P_Wjlj & P_lssjwjm P_Wjlj、P_lssjwjm分别为文件路径与文件名 Adorst.Open P_lssj, Adocon, adOpenStatic, adLockReadOnly, adCmdTable P_lssj：变量，表名 P_lssjjlzs = Adorst.RecordCount 记录总数 If P_lssjjlzs 0 Then P_sjqsj = Adorst!f_rq 第一条记录日期字段内容 Adorst.MoveLast P_zxsjrq = Adorst!f_rq 最后一条记录日期字段内容 End If Adorst.Close Adocon.Close Set Adorst = Nothing Set Adocon = Nothing 说明： Adocon.Open方法中参数：ConnectionString, UserID, Password, Options ConnectionString可选，连接字符串 UserID可选，字符串，包含建立连接时所使用用户名。 Password可选，字符串，包含建立连接时所使用密码。 Options可选，ConnectOptionEnum 值。决定该方法是在连接建立之后（异步）还是连接建立之前（同步）返回。可以是如下某个常量：常量 说明 adConnectUnspecified （默认）同步打开连接。 adAsyncConnect 异步打开连接。ConnectComplete 事件可以用于决定连接何时可用。 Adorst.Open方法中参数：Source, ActiveConnection, CursorType, LockType, Options Source：主要用到两种，一种打开所有记录（直接是表名），一种是部分查询（需要用到SQL语句） CursorType：游标类型。主要用到：AdOpenKeyset：打开键集类型游标；AdOpenStatic：打开静态类型游标。 LockType：锁定类型。主要用到：AdLockReadOnly：只读；AdLockOptimistic：使用开放式锁定，只在调用Update 方法时才更新记录。 Options：说明source的类型。主要用到：adCmdText：source是命令；adCmdTable：表名。 3、查询： select * from 表名 where 字段=值 order by 字段 where：条件。可使用 = = like between 例：ID5 and F_LR like %中国% 序号大于5并且内容中包含“中国” order by：排序 例：order by ID desc 按序号降序排列 日期的使用： Cxtj = select * from & P_lssj & where F_rq between #2005-10-10 10:00:00# and #2005-11-10 10:00:00# 查询前10条记录： Cxtj = select top 10 * from & P_lssj & order by ID Adorst.open cxtj,adocon, adOpenKeyset, adLockOptimistic, adCmdText ( order by ID可以省略) 查询最后10条记录： Cxtj = select top 10 * from & P_lssj & order by ID desc (当数据库中数据太大时，排序需要花很长时间，此时不如先读数据，取得数据总量，然后关闭记录集，再设置合适的条件，打开记录集) 4、查找： Adorst.Find criteria, SkipRows, searchDirection, start Ado的Find方法不支持组合条件查找(And Or)，因此是一种很鸡肋的方法，不如使用select。参数 criteria字符串，包含指定用于搜索的列名、比较操作符和值的语句。 SkipRows 可选，长整型值，其默认值为零，它指定当前行或 start 书签的位移以开始搜索。 searchDirection 可选的 SearchDirectionEnum 值，指定搜索应从当前行还是下一个有效行开始。其值可为 adSearchForward 或 adSearchBackward。搜索是在记录集的开始还是末尾结束由 searchDirection 值决定。 start 可选，变体型书签，用作搜索的开始位置 5、删除： Adocon.Execute (delete from & Gzbm) 删除GZBM表中的所有记录 或： Cxtj=”delete * from ” & gzbm & “ where ID5 and F_LR like %美国%” Adocon.Execute(cxtj) 删除当前记录Gjrst.Delete adAffectCurrent6、增加： Adorst.addnew Adorst.update （例子见：四、EXCEL与ACCESS记录的相互转换） 7、压缩数据库： Sub Yssjk(ByVal Ywjm As String) 压缩数据库 Dim Jrojet As New JRO.JetEngine Dim Mbwjm As String Dim Yml As String, Mbml As String Randomize Mbwjm = P_Wjlj & P_lssj & CStr(Int(Rnd(1) * 1000000) & .mdb Yml = provider=Microsoft.Jet.OLEDB.4.0;data source= & Ywjm & Mbml = provider=Microsoft.Jet.OLEDB.4.0;data source= & Mbwjm & ; On error goto cw Jrojet.CompactDatabase Yml, Mbml On error goto 0 Kill Ywjm 删除 Name Mbwjm As Ywjm 重命名 Exit Sub Cw: On Error GoTo 0 aa = MsgBox(请先关闭数据库文件, vbOKOnly, 提示) End Sub 四、EXCEL与ACCESS记录的相互转换 1、定义 Public Type P_Sjjg 定义数组类型 Sxlx As String Sxsj As String Sxms As String Rq As Date Bdz As String End Type Public P_sj() As P_Sjjg 数组 Public P_sjzs As Integer 数据总数 Dim Adofd as Adodb.Field 字段 2、将EXCEL中的记录写入ACCESS 数组赋值 P_sjzs=Exsheet.cells(65536,1).end(xlup).row-1 ReDim P_sj(P_sjzs) 重定义数组大小 For I = 0 To P_sjzs - 1 P_sj(I).Sxlx = Exsheet.Cells(I + 2, 2) P_sj(I).Sxsj = Exsheet.Cells(I + 2, 3) P_sj(I).Sxms = Exsheet.Cells(I + 2, 4) Next I 写入数据库 For I = 0 To P_sjzs - 1 Adorst.AddNew Adorst!f_sxlx = P_sj(I).Sxlx Adorst!f_rq = P_sj(I).Rq Adorst!f_bdz = P_sj(I).Bdz Adorst.Update Next I 3、将ACCESS中的记录写入EXCEL 、使用记录集 打开记录集 写入EXCEL For Each Adofd in adorst.Fields Exsheet.cells(1,J)=Adofd.Name 字段名 J=J+1 next Exsheet.a2.CopyFromRecordset Adorst 、使用数组： 数组赋值 exsheet.Range(左上单元格,右下单元格).value=数组名 、使用文本文件过渡（摘自：学习微软 Excel 2002 VBA 编程和XML，ASP技术第十五章） ACCESS写入文本文件(用Tab键分隔) Cxtj = SELECT * FROM & P_lssj & WHERE ID 50 Set Adorst = Adocon.Execute(CommandText:=Cxtj, Options:=adCmdText) strData = adorst.GetString(StringFormat:=adClipString, ColumnDelimeter:=vbTab, RowDelimeter:=vbCr, nullExpr:=vbNullString) Open C:Produc