VB6 連接 Oracle 資料庫完整程式碼範例：ADO 開啟、寫入、關閉

完整程式碼模組

'=== 模組級變數宣告 ===
Dim OraDB As ADODB.Connection
Dim OraRS As ADODB.Recordset
Dim OraDB_Open As Boolean
Dim OraConstr As String
Dim strOraPWD As String, strOraUser As String, OraDBNetName As String
Dim strOraPID As String, strName As String, strPsex As String
Dim OraDBtablename As String

'=== 開啟資料庫連線 ===
Public Sub OpenOraDB()
    On Error GoTo ToExit
    OraDB_Open = False
    Set OraDB = New ADODB.Connection
    
    ' 連接字串：使用 OraOLEDB.Oracle Provider
    OraConstr = "Provider=OraOLEDB.Oracle.1;Password=" & strOraPWD & _
                ";User ID=" & strOraUser & _
                ";Data Source=" & OraDBNetName & _
                ";Persist Security Info=False"
    
    OraDB.CursorLocation = adUseServer
    OraDB.Open OraConstr
    OraDB_Open = True
    Exit Sub

ToExit:
    ' MsgBox "連接資料庫伺服器錯誤...", vbInformation, "錯誤訊息"
    OraDB_Open = False
End Sub

'=== 關閉資料庫連線 ===
Public Sub CloseOraDB()
    If OraDB_Open = True Then
        If (OraDB.State = adStateOpen) Then
            OraDB.Close
            Set OraDB = Nothing
            OraDB_Open = False
        End If
    End If
End Sub

'=== 開啟 Recordset 並寫入新資料 ===
Public Sub InsertData()
    Set OraRS = New ADODB.Recordset
    OraRS.ActiveConnection = OraDB
    OraRS.CursorLocation = adUseServer
    OraRS.LockType = adLockBatchOptimistic
    
    strOraRS = "SELECT * FROM " & OraDBtablename
    OraRS.Open strOraRS, OraDB, adOpenStatic, adLockOptimistic
    
    OraRS.AddNew
    OraRS.Fields("PID") = strOraPID
    OraRS.Fields("PNAME") = strName      ' 注意：原碼有語法錯誤，已修正
    OraRS.Fields("PSEX") = strPsex
    OraRS.Update
    
    '=== 關閉 Recordset ===
    OraRS.Close
    Set OraRS = Nothing
End Sub

關鍵點說明

常見問題排查

• 「找不到提供者」：確認客戶端已安裝 Oracle Client 並註冊 OraOLEDB.Oracle。
• TNS 無法解析：檢查 tnsnames.ora 設定，Data Source 需符合 TNS 別名。
• 欄位名稱大小寫：Oracle 預設大寫，VB 代碼中 Fields("PNAME") 建議統一大寫。