作者:lianghc
描述:最近在使用infamatica 将excel 的数据导入oracle时,遇到意外终止错误,无法将数据导入。于是采用VBA将数据导入数据库,是办公人员一键同步excel的数据导数据库中,这种做法的前提是提供标准的模板。下面是解决问题过程中收集的连接数据库的方法,整理一下供大家参考。
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 &#