完整 VB6/VBA 連接 Oracle 代碼模組
' ==========================================
' 模組級變數宣告
' ==========================================
Option Explicit
Private OraDB As ADODB.Connection
Private OraRS As ADODB.Recordset
Private OraDB_Open As Boolean
Private OraConstr As String
Private strOraUser As String
Private strOraPWD As String
Private OraDBNetName As String
Private strOraPID As String
Private strName As String
Private strPsex As String
Private OraDBtablename As String
' ==========================================
' 開啟資料庫連線
' ==========================================
Public Sub OpenOraDB()
On Error GoTo ToExit
OraDB_Open = False
Set OraDB = New ADODB.Connection
' OLE DB Provider for Oracle 連接字串
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
Err.Raise vbObjectError + 513, "OpenOraDB", Err.Description
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
' ==========================================
' 開啟記錄集並寫入資料
' ==========================================
Public Sub InsertData()
On Error GoTo ErrHandler
Set OraRS = New ADODB.Recordset
OraRS.ActiveConnection = OraDB
OraRS.CursorLocation = adUseServer
OraRS.LockType = adLockBatchOptimistic
' 動態 SQL
Dim strOraRS As String
strOraRS = "SELECT * FROM " & OraDBtablename
' 開啟靜態游標、樂觀鎖定
OraRS.Open strOraRS, OraDB, adOpenStatic, adLockOptimistic
' 新增記錄
OraRS.AddNew
OraRS.Fields("PID").Value = strOraPID
OraRS.Fields("pname").Value = strName
OraRS.Fields("psex").Value = strPsex
OraRS.Update
' 關閉記錄集
OraRS.Close
Set OraRS = Nothing
Exit Sub
ErrHandler:
If Not OraRS Is Nothing Then
If OraRS.State = adStateOpen Then OraRS.Close
Set OraRS = Nothing
End If
Err.Raise vbObjectError + 514, "InsertData", Err.Description
End Sub
關鍵點解析
| 項目 | 說明 |
| Provider | OraOLEDB.Oracle.1 為 Oracle 官方 OLE DB 提供者,需安裝 Oracle Client |
| CursorLocation | adUseServer 伺服器端游標,適合大量資料;adUseClient 客戶端游標支援離線操作 |
| LockType | adLockBatchOptimistic 批次樂觀鎖定,適合斷線式更新;adLockOptimistic 即時樂觀鎖定 |
| CursorType | adOpenStatic 靜態游標(快照),不反映他人變更;adOpenKeyset/adOpenDynamic 反映度較高 |
| 錯誤處理 | 使用 On Error GoTo 統一跳轉,確保資源在異常時正確釋放 |
現代化建議 (VB.NET / C#)
' VB.NET 使用 ODP.NET (Oracle.ManagedDataAccess)
Imports Oracle.ManagedDataAccess.Client
Using conn As New OracleConnection("User Id=scott;Password=tiger;Data Source=orcl;")
conn.Open()
Using cmd = conn.CreateCommand()
cmd.CommandText = "INSERT INTO employees (emp_id, emp_name) VALUES (:id, :name)"
cmd.Parameters.Add(":id", 1001)
cmd.Parameters.Add(":name", "John Chen")
cmd.ExecuteNonQuery()
End Using
End Using
- 優先使用 ODP.NET Managed Driver(無需安裝 Oracle Client,單一 DLL 部署)
- 採用
Using 區塊自動釋放資源
- 參數化查詢防止 SQL 注入
- 非同步模式
await cmd.ExecuteNonQueryAsync() 提升 UI 回應
常見連線問題
- ORA-12154: TNS:could not resolve the connect identifier specified
tnsnames.ora 設定錯誤,或 Data Source 名稱拼寫錯誤
- ORA-12541: TNS:no listener
- 監聽器未啟動,
lsnrctl start
- Provider cannot be found
- 未安裝 Oracle Client / OLE DB Provider,或位元數不符(32-bit App 需 32-bit Client)
- 權限不足
- 用戶缺乏
CREATE SESSION、INSERT 權限