首頁>Club>
12
回覆列表
  • 1 # 風紫子

    方法如下步驟操作:

      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完成資料處理

  • 中秋節和大豐收的關聯?
  • 唐朝詩人韓栩?