【VBA×SQL Server】業務を一歩進める！上級者向け・高度な接続制御テクニック

こんにちは、なかぜんです。
AccessやVBAで業務システムを組んでいると、「SQL Serverとしっかり連携させたい」と思う場面がありますよね。でも、接続が切れる、トランザクションがうまくいかない、パフォーマンスが気になる……。そんな悩み、ありませんか？

今回は、Access VBAからSQL Serverを操作する上で押さえておきたい「接続制御の高度なテクニック」をお届けします。安定した処理、堅牢な業務アプリを作りたい上級者の方におすすめの内容です！

1. VBAからSQL Serverに接続する基本構成
1. ADODB接続の基本形
2. Windows認証の場合
2. 高度な接続制御のテクニック
3. よくあるミスと注意点
4. 応用テクニック
5. まとめと次のステップ

1. VBAからSQL Serverに接続する基本構成

ADODB接続の基本形

VBAからSQL Serverへ接続するには、ADODB.Connectionを使うのが一般的です。

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

conn.ConnectionString = _
    "Provider=SQLOLEDB;Data Source=サーバー名;Initial Catalog=データベース名;" & _
    "User ID=ユーザー名;Password=パスワード;"

conn.Open

ポイント解説：

Provider=SQLOLEDB：OLE DBプロバイダを指定します（新しい環境ではMSOLEDBSQLも検討）
Data Source：SQL Serverの名前（IPでもOK）
Initial Catalog：使用するデータベース名
User ID / Password：SQL Server認証用のログイン情報

Windows認証の場合

ドメイン環境では、以下のようにWindows認証も可能です。

conn.ConnectionString = _
    "Provider=SQLOLEDB;Data Source=サーバー名;Initial Catalog=データベース名;Integrated Security=SSPI;"

2. 高度な接続制御のテクニック

接続の再利用

複数の処理で同じ接続を使い回すことで、パフォーマンスと安定性が向上します。

' モジュールレベルで宣言
Public g_conn As ADODB.Connection

Public Sub InitConnection()
    Set g_conn = New ADODB.Connection
    g_conn.ConnectionString = "(略)"
    g_conn.Open
End Sub

トランザクション制御

業務処理で複数のSQL文を安全に実行したいときは、トランザクションを使います。

g_conn.BeginTrans

On Error GoTo RollbackProc

' SQL文を実行

' 正常終了
g_conn.CommitTrans
Exit Sub

RollbackProc:
    g_conn.RollbackTrans
    MsgBox "処理中にエラーが発生しました。ロールバックしました。"

接続切れ対策（再接続処理）

まれに接続が切れてしまうケースへの対策コードです。

If g_conn Is Nothing Or g_conn.State = adStateClosed Then
    InitConnection
End If

3. よくあるミスと注意点

接続が閉じられずにメモリリーク → conn.CloseとSet conn = Nothingを忘れずに
接続文字列にミス → 特にセミコロン漏れやスペルミスに注意
同時実行によるロック → 必要に応じてロックレベルやタイムアウトを設定

4. 応用テクニック

レコードセットの活用

読み取り専用で高速に処理したいときには以下のようにします。

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM 顧客", g_conn, adOpenForwardOnly, adLockReadOnly

ストアドプロシージャ呼び出し

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
    .ActiveConnection = g_conn
    .CommandType = adCmdStoredProc
    .CommandText = "sp_受注登録"
    .Parameters.Refresh
    .Parameters("@顧客ID") = 123
    .Execute
End With