vb控制ACCESS及EXCEL
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) '设置当前工作
表
关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf
为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
方法
快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载
:
1
(具体参见下一部分)
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 事件可以用于决定连接何时可用。
2
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
例:ID>5 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、查找:
criteria, SkipRows, searchDirection, start Adorst.Find
Ado的Find方法不支持组合条件查找(And Or),因此是一种很鸡肋的方法,不如使用select。
参数
criteria 字符串,包含指定用于搜索的列名、比较操作符和值的语句。
SkipRows 可选,长整型值,其默认值为零,它指定当前行或 start
书
关于书的成语关于读书的排比句社区图书漂流公约怎么写关于读书的小报汉书pdf
签的位移以开始搜索。
searchDirection 可选的 SearchDirectionEnum 值,指定搜索应从当前行还是下一个有效行开始。其值可为 adSearchForward 或 adSearchBackward。搜索是在记录集的开始还是末尾结束由 searchDirection 值决定。
start 可选,变体型书签,用作搜索的开始位置
5、删除:
Adocon.Execute ("delete from " & Gzbm) ‘删除GZBM表中的所有记录
或:
Cxtj=”delete * from ” & gzbm & “ where ID>5 and F_LR like ‘%美国%’”
3
Adocon.Execute(cxtj)
‘删除当前记录
Gjrst.Delete adAffectCurrent
6、增加:
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 ‘字段
4
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)
5
Open "C:\ProductsOver50.txt" For Output As #1 For Each Adofd In Adorst.Fields
strHeader = strHeader + Adofd.Name & vbTab Next
Print #1, strHeader
Print #1, strData
Close #1
‘用EXCEL打开文本文件(录制宏即可)
„„
‘删除文本文件
kill "C:\ProductsOver50.txt"
RST2.Fields("empl_name").Value = RST1.Fields("empl_name").Value
6