Windows10系统之家 - 安全纯净无插件系统之家win10专业版下载安装

收藏本站

Windows10系统下载

Windows10系统下载

全自动无人值守

全新的视觉体验

大小:3.98 GB 版本:32位 大小:4.98 GB 版本:64位
您当前的位置:Windows10绯荤粺涔嬪 > win10资讯 > vba连接oracle方法?

vba连接oracle方法?

时间:2024-06-18 06:40:49 来源:Windows10之家 作者:admin

方法如下步骤操作:

  1、打开VBA编辑器,在菜单中点选“工具”,“引用”;

  2、确保“Microsoft ActiviteX Data Objects 2.8 Library”和“Microsoft ActiviteX Data ObjectS Recordset 2.8 Library”被勾选上。

  建立连接过程,代码如下:

复制代码代码如下:

Public Sub ConOra()

On Error GoTo ErrMsg:

Dim ConnDB As ADODB.Connection

Set ConnDB = New ADODB.Connection

Dim ConnStr As String

Dim DBRst As ADODB.Recordset

Set DBRst = New ADODB.Recordset

Dim SQLRst As String

Dim OraOpen As Boolean

OraOpen = False

OraID="Orcl" 'Oracle数据库的相关配置

OraUsr="user"

OraPwd="password"

ConnStr = "Provider = MSDAORA.1;Password=" & OraPwd & _

";User ID=" & OraUsr & _

";Data Source=" & OraID & _

";Persist Security Info=True"

ConnDB.CursorLocation = adUseServer

ConnDB.Open ConnStr

OraOpen = True '成功执行后,数据库即被打开

'MsgBox "Connect to the oracle database Successful!", vbInformation, "Connect Successful"

DBRst.ActiveConnection = ConnDB

DBRst.CursorLocation = adUseServer

DBRst.LockType = adLockBatchOptimistic

SQLRst = "Select * From TstTab"

DBRst.Open SQLRst, ConnDB, adOpenStatic, adLockBatchOptimistic

DBRst.MoveFirst

Exit Function

ErrMsg:

OraOpen = False

MsgBox "Connect to the oracle database fail ,please check!", vbCritical, "Connect fail!"

End Function

Public Sub ConOra()

On Error GoTo ErrMsg:

Dim ConnDB As ADODB.Connection

Set ConnDB = New ADODB.Connection

Dim ConnStr As String

Dim DBRst As ADODB.Recordset

Set DBRst = New ADODB.Recordset

Dim SQLRst As String

Dim OraOpen As Boolean

OraOpen = False

OraID="Orcl" 'Oracle数据库的相关配置

OraUsr="user"

OraPwd="password"

ConnStr = "Provider = MSDAORA.1;Password=" & OraPwd & _

";User ID=" & OraUsr & _

";Data Source=" & OraID & _

";Persist Security Info=True"

ConnDB.CursorLocation = adUseServer

ConnDB.Open ConnStr

OraOpen = True '成功执行后,数据库即被打开

'MsgBox "Connect to the oracle database Successful!", vbInformation, "Connect Successful"

DBRst.ActiveConnection = ConnDB

DBRst.CursorLocation = adUseServer

DBRst.LockType = adLockBatchOptimistic

SQLRst = "Select * From TstTab"

DBRst.Open SQLRst, ConnDB, adOpenStatic, adLockBatchOptimistic

DBRst.MoveFirst

Exit Function

ErrMsg:

OraOpen = False

MsgBox "Connect to the oracle database fail ,please check!", vbCritical, "Connect fail!"

End Function

可以根据需要调整SQL语句,获取相关数据,并输出到Excel完成数据处理