作者:iamlaosong
1、引用法
也就是(外部控件)前期绑定,好处是绑定后编写代码时能够自动列出成员信息,包括属性、方法及参数列表。
引用ADO相关组件:打开VBA编辑器,在菜单中点选“工具”--》“引用”。确保“Microsoft ActiviteX Data Objects 2.8 Library”和“Microsoft ActiviteX Data ObjectS Recordset 2.8 Library”被勾选上。引用后再声明:
Dim cnn As New Connection '声明链接对象
Dim rst As New Recordset '声明记录集对象
下面是例程:
Dim cnn As New Connection
Dim rst As New Recordset
cnn.Open "Provider=msdaora.1;Data Source=dl580;User Id=emssxjk;Password=emssxjk;"
OraOpen = True '成功执行后,数据库即被打开
sqls = "select count(*) from tb_evt_dlv where mail_num='" & emsid & "'"
Set rst = cnn.Execute(sqls)
If rst(0) > 0 Then
sqls = "select b.zj_code,b.zj_mc,b.jgfl,b.city,b.ssxs from tb_evt_dlv a, tb_jg b "
sqls = sqls & "where a.dlv_bureau_org_code = b.zj_code and a.mail_num='" & emsid & "' and rownum=1"
Set rst = cnn.Execute(sqls)
sqls = "CopyFromRecordset"
'maxrow = Sheets(qfxx).[A65536].End(xlUp).Row
'If maxrow > 1 Then Sheets(qfxx).Range("a2:H" & maxrow).ClearContents
Cells(row1, pos_sav).CopyFromRecordset rst
Else
sqls = "select b.zj_code,b.zj_mc,b.jgfl,b.city,b.ssxs from tb_evt_mail_clct a, tb_jg b "
sqls = sqls & "where a.clct_bureau_org_code = b.zj_code and a.mail_num='" & emsid & "' and rownum=1"
Set rst = cnn.Execute(sqls)
sqls = "CopyFromRecordset"
'maxrow = Sheets(qfxx).[A65536].End(xlUp).Row
'If maxrow > 1 Then Sheets(qfxx).Range("a2:H" & maxrow).ClearContents
Cells(row1, pos_sav + 5).CopyFromRecordset rst
End If
2、创建法
也就是(外部控件)后期绑定,因不需要引用ADO相关组件,直接使用CreateObject函数创建ADO对象,使用者可以直接使用。本方法需要知道创建对象的名称,即:
Set cnn = CreateObject("ADODB.connection") '创建ado对象
Set rst = CreateObject("ADODB.recordset") '创建记录集
下面是例程(和上面例程类似,前半部分不同,后面的相同):
Dim cnn As Object, rst As Object
Set cnn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
cnn.Open "Provider=msdaora.1;Data Source=dl580;User Id=emssxjk;Password=emssxjk;"
OraOpen = True '成功执行后,数据库即被打开
其它组件的使用也和这个差不多,建议用创建法,这样就不用管“引用”中的设置了,例如:
Dim dic As Object '直接创建不需要引用
Set dic = CreateObject("scripting.dictionary") '创建字典对象
Dim fso as Object '直接创建不需要引用
Set fso = CreateObject("Scripting.FileSystemObject") '创建文件对象模型