国产探花免费观看_亚洲丰满少妇自慰呻吟_97日韩有码在线_资源在线日韩欧美_一区二区精品毛片,辰东完美世界有声小说,欢乐颂第一季,yy玄幻小说排行榜完本

首頁 > 數(shù)據(jù)庫 > Oracle > 正文

Excel VBA連接并操作Oracle

2024-08-29 13:56:15
字體:
供稿:網(wǎng)友
以下是通過Excel 的VBA連接Oracle并操作Oracle相關(guān)數(shù)據(jù)的示例
Excel 通過VBA連接數(shù)據(jù)庫需要安裝相應(yīng)的Oracle客戶端工具并引用ADO的相關(guān)組件,引用ADO相關(guān)組件可按如下步驟操作:
  1、打開VBA編輯器,在菜單中點(diǎn)選“工具”,“引用”;
  2、確保“Microsoft ActiviteX Data Objects 2.8 Library”和“Microsoft ActiviteX Data ObjectS Recordset 2.8 Library”被勾選上。
  建立連接過程,代碼如下:

復(fù)制代碼 代碼如下:


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數(shù)據(jù)庫的相關(guān)配置
OraUsr="user"
OraPwd="password"
ConnStr = "Provider = MSDAORA.1;Password=" & OraPwd & _
";User;Data Source=" & OraID & _
";Persist Security Info=True"
ConnDB.CursorLocation = adUseServer
ConnDB.Open ConnStr
OraOpen = True '成功執(zhí)行后,數(shù)據(jù)庫即被打開
'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數(shù)據(jù)庫的相關(guān)配置
OraUsr="user"
OraPwd="password"
ConnStr = "Provider = MSDAORA.1;Password=" & OraPwd & _
";User;Data Source=" & OraID & _
";Persist Security Info=True"
ConnDB.CursorLocation = adUseServer
ConnDB.Open ConnStr
OraOpen = True '成功執(zhí)行后,數(shù)據(jù)庫即被打開
'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


可以根據(jù)需要調(diào)整SQL語句,獲取相關(guān)數(shù)據(jù),并輸出到Excel完成數(shù)據(jù)處理
上述代碼在Windows XP SP3/2003 SP2 + Office2003下測(cè)試通過.
發(fā)表評(píng)論 共有條評(píng)論
用戶名: 密碼:
驗(yàn)證碼: 匿名發(fā)表
主站蜘蛛池模板: 廉江市| 灵台县| 迭部县| 青阳县| 合江县| 保德县| 宜宾市| 栖霞市| 兴义市| 河间市| 阿合奇县| 固镇县| 平果县| 醴陵市| 边坝县| 石台县| 翁牛特旗| 抚州市| 平阳县| 泰顺县| 岢岚县| 渝北区| 阳西县| 和龙市| 临沭县| 福海县| 清镇市| 徐汇区| 深水埗区| 合川市| 红原县| 育儿| 泗阳县| 碌曲县| 泾阳县| 天祝| 惠东县| 扎兰屯市| 临高县| 德阳市| 阜阳市|