VB6 / VBA 連接 SQL Server 連接字串完整範例：含 Windows 整合驗證與錯誤處理

Visual Basic 6.0 / VBA (Access/Excel) 透過 ADO 連接 SQL Server，常見連接字串模式與錯誤處理範本。

連接字串模式

完整 VB6 模組程式碼範本

' 標準模組 modDB.bas
Option Explicit

Public gConn As ADODB.Connection
Public Const gConnStr As String = "Provider=SQLOLEDB.1;Data Source=192.168.1.50;Initial Catalog=HumanDB;Integrated Security=SSPI;"

Public Sub DB_Connect()
    On Error GoTo ErrHandler
    If gConn Is Nothing Then Set gConn = New ADODB.Connection
    If gConn.State = adStateOpen Then Exit Sub
    
    gConn.ConnectionString = gConnStr
    gConn.CommandTimeout = 30
    gConn.ConnectionTimeout = 15
    gConn.CursorLocation = adUseClient  ' 客戶端游標，支援離線 Recordset
    gConn.Open
    Exit Sub

ErrHandler:
    Select Case Err.Number
        Case -2147467259, -2147217900  ' SQL Server 不存在/未啟動
            MsgBox "資料庫伺服器未啟動或無法連線，請檢查網路與 SQL Server 服務。", vbCritical, "連線失敗"
        Case -2147217843  ' 登入失敗
            MsgBox "資料庫登入失敗，請確認帳號密碼或 Windows 權限。", vbCritical, "登入失敗"
        Case Else
            MsgBox "資料庫連線錯誤: " & Err.Number & " - " & Err.Description, vbCritical, "錯誤"
    End Select
    Set gConn = Nothing
End Sub

Public Sub DB_Disconnect()
    On Error Resume Next
    If Not gConn Is Nothing Then
        If gConn.State = adStateOpen Then gConn.Close
        Set gConn = Nothing
    End If
End Sub

' 範例：執行無回傳 SQL (INSERT/UPDATE/DELETE)
Public Function ExecSQL(ByVal sql As String, ParamArray params() As Variant) As Long
    On Error GoTo ErrExec
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = gConn
        .CommandText = sql
        .CommandType = adCmdText
        .CommandTimeout = 60
        ' 參數綁定 (簡易示範，實際建議用 CreateParameter)
        Dim i As Long
        For i = LBound(params) To UBound(params) Step 2
            .Parameters.Append .CreateParameter(CStr(params(i)), adVariant, adParamInput, , params(i + 1))
        Next
    End With
    ExecSQL = cmd.Execute(RecordsAffected:=ExecSQL)
    Exit Function

ErrExec:
    MsgBox "SQL 執行錯誤: " & Err.Description & vbCrLf & sql, vbCritical
    ExecSQL = -1
End Function

' 範例：查詢回傳 Recordset
Public Function QueryRS(ByVal sql As String, ParamArray params() As Variant) As ADODB.Recordset
    On Error GoTo ErrQuery
    Dim cmd As ADODB.Command, rs As ADODB.Recordset
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = gConn
        .CommandText = sql
        .CommandType = adCmdText
        Dim i As Long
        For i = LBound(params) To UBound(params) Step 2
            .Parameters.Append .CreateParameter(CStr(params(i)), adVariant, adParamInput, , params(i + 1))
        Next
    End With
    Set rs = New ADODB.Recordset
    rs.CursorType = adOpenStatic
    rs.LockType = adLockReadOnly
    rs.Open cmd
    Set QueryRS = rs
    Exit Function

ErrQuery:
    MsgBox "查詢錯誤: " & Err.Description, vbCritical
    Set QueryRS = Nothing
End Function

表單中使用範例

Private Sub Form_Load()
    Call DB_Connect
    Dim rs As ADODB.Recordset
    Set rs = QueryRS("SELECT EmpID, EmpName FROM Employees WHERE DeptID = ?", "@DeptID", 10)
    Do While Not rs.EOF
        lstEmployees.AddItem rs("EmpName")
        rs.MoveNext
    Loop
    rs.Close
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Call DB_Disconnect
End Sub

關鍵最佳實務

1. 參數化查詢：Command.Parameters 防 SQL Injection、自動處理型別/日期格式。
2. 連接物件全域單例：應用程式生命週期內重用同一 Connection，利用連接池。
3. 錯誤處理分級：針對常見錯誤碼給予使用者友善提示，詳細錯誤寫入日誌。
4. 游標位置：adUseClient 適合離線操作、排序/篩選；adUseServer 適合大量資料流式讀取。
5. 資源釋放：Recordset.Close、Set rs = Nothing、Connection.Close。

常見問題排查

• 找不到 Provider：安裝 Microsoft Data Access Components (MDAC) 2.8+ 或 Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL)。
• 登入失敗：確認 SQL Server 混合模式、帳號鎖定、防火牆 1433、SPN/Kerberos (整合驗證跨網域)。
• 中文亂碼：連接字串加 ;Codepage=65001，資料表欄位用 NVARCHAR，VB6 專案設定 Unicode 支援。

VB6/VBA 雖屬經典技術，但大量內部系統仍在運作。規範連接管理與參數化查詢，能延長系統壽命並大幅降低安全風險。