64 vba 连接oracle,Excel VBA连接并操作Oracle

Excel VBA连接并操作Oracle

2019-01-03

编程之家收集整理的这篇文章主要介绍了Excel VBA连接并操作Oracle，编程之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

以下是通过Excel 的VBA连接Oracle并操作Oracle相关数据的示例@H_403_0@Excel 通过VBA连接数据库需要安装相应的Oracle客户端工具并引用ADO的相关组件，引用ADO相关组件可按如下步骤操作： @H_403_0@　　1、打开VBA编辑器，在菜单中点选“工具”，“引用”； @H_4030@　　2、确保“Microsoft ActiviteX Data Objects 2.8 Library”和“Microsoft ActiviteX Data ObjectS Recordset 2.8 Library”被勾选上。 @H403_0@　　建立连接过程，代码如下： @H_403_0@

代码如下:

@H_4030@Public Sub ConOra() @H4030@On Error GoTo ErrMsg: @H4030@Dim ConnDB As ADODB.Connection @H4030@Set ConnDB = New ADODB.Connection @H4030@Dim ConnStr As String @H4030@Dim DBRst As ADODB.Recordset @H4030@Set DBRst = New ADODB.Recordset @H403_0@Dim sqlRst As String @H_4030@Dim OraOpen As Boolean @H4030@OraOpen = False @H403_0@OraID="Orcl" 'Oracle数据库的相关配置 @H_4030@OraUsr="user" @H4030@OraPwd="password" @H4030@ConnStr = "Provider = MSDAORA.1;Password=" & OraPwd & @H_4030@";User ID=" & OraUsr & @H_4030@";Data Source=" & OraID & @H_4030@";Persist Security Info=True" @H4030@ConnDB.CursorLocation = adUseServer @H4030@ConnDB.Open ConnStr @H403_0@OraOpen = True '成功执行后，数据库即被打开 @H_403_0@'MsgBox "Connect to the oracle database Successful!",vbInformation,"Connect Successful" @H_4030@DBRst.ActiveConnection = ConnDB @H4030@DBRst.CursorLocation = adUseServer @H4030@DBRst.LockType = adLockBatchOptimistic @H403_0@sqlRst = "Select * From TstTab" @H_403_0@DBRst.Open sqlRst,ConnDB,adOpenStatic,adLockBatchOptimistic @H_4030@DBRst.MoveFirst @H4030@Exit Function @H4030@ErrMsg: @H4030@OraOpen = False @H403_0@MsgBox "Connect to the oracle database fail,please check!",vbCritical,"Connect fail!" @H_4030@End Function @H4030@Public Sub ConOra() @H4030@On Error GoTo ErrMsg: @H4030@Dim ConnDB As ADODB.Connection @H4030@Set ConnDB = New ADODB.Connection @H4030@Dim ConnStr As String @H4030@Dim DBRst As ADODB.Recordset @H4030@Set DBRst = New ADODB.Recordset @H403_0@Dim sqlRst As String @H_4030@Dim OraOpen As Boolean @H4030@OraOpen = False @H403_0@OraID="Orcl" 'Oracle数据库的相关配置 @H_4030@OraUsr="user" @H4030@OraPwd="password" @H4030@ConnStr = "Provider = MSDAORA.1;Password=" & OraPwd & @H_4030@";User ID=" & OraUsr & @H_4030@";Data Source=" & OraID & @H_4030@";Persist Security Info=True" @H4030@ConnDB.CursorLocation = adUseServer @H4030@ConnDB.Open ConnStr @H403_0@OraOpen = True '成功执行后，数据库即被打开 @H_403_0@'MsgBox "Connect to the oracle database Successful!","Connect fail!" @H_4030@End Function @H4030@