【実務・中級編】ADOでSQL Serverのビューを更新する：INSTEAD OFトリガーとの連携とVBA側の対応 – Access VBA解析バイブル

「読み取り専用」の壁を突破せよ：ADOでSQL Serverのビューを更新する、プロフェッショナル・アーキテクチャ

Access開発の現場において、ある種の「中級者」が必ず直面する壁がある。それは、「SQL Server上のビュー（View）を介したデータ更新」だ。

「ビューを作ったのだから、そこに対して `Recordset.Update` を投げれば更新できるはずだ」
そんな安易な考えでコードを書くと、実行時に `Operation not allowed when the object is read-only`（オブジェクトが読み取り専用であるため、操作は許可されません）という無慈悲なエラーに叩き落とされることになる。

なぜこれが起きるのか？ そして、なぜDAOではなくADOを使うのか？
今回は、単なるエラー回避術ではない。サーバー側の「INSTEAD OFトリガー」と、クライアント側の「堅牢なADO制御」を組み合わせた、エンタープライズ級の設計思想を伝授する。

1. 根本的な設計思想の乖離：DAOとADOの決定的な違い

まず、君たちが使っている武器の違いを正しく理解せよ。

• DAO (Data Access Objects): Accessのローカルエンジン（Jet/ACE）に特化している。ローカルテーブルへの操作には極めて高速だが、ネットワーク越し、あるいはSQL Serverのような外部エンジンを操作する設計には向かない。
• ADO (ActiveX Data Objects): ネットワーク越し、あるいは異なるデータベースエンジンへアクセスするための「通信プロトコル」に近い存在だ。

ビューの更新でエラーが出る最大の理由は、「SQL Server側が、そのビューのどの列がどの基底テーブルのどの列に対応するかを判断できない（あるいは判断を拒否している）」からだ。結合（JOIN）を含むビューの場合、更新対象が曖昧になるため、SQL Serverは安全策としてビューを「読み取り専用」として扱う。

この「曖昧さ」を排除し、クライアント（Access）からは透過的に、サーバー側で確実に処理を完結させるのが、プロフェッショナルな設計である。

2. サーバー側の防衛策：INSTEAD OF トリガーの導入

ビューを更新可能にするための正攻法は、SQL Server側に `INSTEAD OF` トリガー を実装することだ。

これは、「もしこのビューに対して INSERT/UPDATE/DELETE が行われたら、その命令を実行する代わりに（INSTEAD OF）、私が書いたこのロジックを実行せよ」という命令である。これにより、ビューは単なる「窓」ではなく、高度な「制御インターフェース」へと昇華する。

【SQL Server側】実装例

例えば、`Orders`（注文）と `OrderDetails`（注文明細）を結合したビュー `vw_OrderFull` があるとする。

-- 1. 結合ビューの作成
CREATE VIEW vw_OrderFull AS
SELECT 
    o.OrderID, 
    o.OrderDate, 
    d.DetailID, 
    d.ProductID, 
    d.Quantity
FROM Orders o
JOIN OrderDetails d ON o.OrderID = d.OrderID;
GO

-- 2. INSTEAD OF UPDATE トリガーの実装
-- ビューへの更新命令をインターセプトし、基底テーブルへ振り分ける
CREATE TRIGGER trg_vw_OrderFull_Update
ON vw_OrderFull
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Ordersテーブル（親）の更新
    UPDATE o
    SET o.OrderDate = i.OrderDate
    FROM Orders o
    INNER JOIN inserted i ON o.OrderID = i.OrderID;

    -- OrderDetailsテーブル（子）の更新
    UPDATE d
    SET d.ProductID = i.ProductID,
        d.Quantity = i.Quantity
    FROM OrderDetails d
    INNER JOIN inserted i ON d.DetailID = i.DetailID;
END;
GO

これで、Access側からは「結合されたビュー」に対して単一の `Update` 命令を投げるだけで、SQL Serverが裏側で適切に2つのテーブルを更新してくれるようになる。

3. クライアント側の掌握：ADOによる堅牢な実装

サーバー側が準備できたら、次はAccess（VBA）側の実装だ。
ここで初心者が犯すミスは、「トランザクション管理の放棄」と「カーソル位置の誤認」である。

ネットワーク越しに複数のテーブルを更新する場合、万が一途中でエラーが起きたらどうなるか？ 親テーブルだけ更新され、子テーブルが更新されない「データの不整合」が発生する。これはシステムにおける死を意味する。

【Access VBA】プロダクション・クオリティのコード例

以下のコードは、エラーハンドリング、トランザクション、およびADOの最適なカーソル設定を組み込んだ、実戦でそのまま使えるテンプレートだ。

Option Compare Database
Option Explicit

' ======================================================================
' 業務概要: SQL Server上のビューを介して注文情報を更新する
' 設計思想: 
'   1. ADOを使用したサーバーサイドへの直接命令
'   2. 明示的なトランザクション管理によるデータ整合性の担保
'   3. Client-side Cursorによるレコードセット操作の安定化
' ======================================================================

Public Sub UpdateOrderViaView(ByVal targetOrderID As Long, ByVal newDate As Date, ByVal newQty As Integer)
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strConn As String
    Dim strSQL As String
    
    ' 接続文字列（環境に合わせて適切に設定すること）
    strConn = "Provider=MSOLEDBSQL;Data Source=YOUR_SERVER;Initial Catalog=YOUR_DB;Integrated Security=SSPI;"
    
    On Error GoTo ErrorHandler

    ' 1. Connectionオブジェクトの生成とオープン
    Set cn = New ADODB.Connection
    cn.ConnectionString = strConn
    cn.Open
    
    ' 【重要】トランザクションの開始
    ' サーバー側での一連の更新処理をアトミック（不可分）にする
    cn.BeginTrans
    
    ' 2. Recordsetの生成
    Set rs = New ADODB.Recordset
    strSQL = "SELECT * FROM vw_OrderFull WHERE OrderID = " & targetOrderID
    
    ' 【重要】CursorLocationをadUseClientに設定
    ' これにより、レコードセットがクライアント側にキャッシュされ、
    ' 複雑な結合ビューに対しても安定した更新操作が可能になる
    rs.CursorLocation = adUseClient
    
    ' 3. レコードの取得
    ' LockTypeはadLockOptimistic（楽観的ロック）を使用
    rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic
    
    If Not rs.EOF Then
        ' 4. データの更新
        ' サーバー側のINSTEAD OFトリガーがこの更新を検知する
        rs.Fields("OrderDate").Value = newDate
        rs.Fields("Quantity").Value = newQty
        
        ' 更新の確定
        rs.Update
        
        ' 5. コミット（成功時）
        cn.CommitTrans
        Debug.Print "Update Successful: OrderID " & targetOrderID
    Else
        MsgBox "対象のレコードが見つかりません。", vbExclamation
        cn.RollbackTrans
    End If

CleanUp:
    ' オブジェクトの解放（メモリリーク防止）
    If Not rs Is Nothing Then
        If rs.State = adStateOpen Then rs.Close
    End If
    Set rs = Nothing
    
    If Not cn Is Nothing Then
        If cn.State = adStateOpen Then cn.Close
    End If
    Set cn = Nothing
    Exit Sub

ErrorHandler:
    ' 6. ロールバック（エラー発生時）
    ' 何らかの理由で更新に失敗した場合、全ての変更を取り消す
    If Not cn Is Nothing Then
        If cn.State = adStateOpen Then
            cn.RollbackTrans
            Debug.Print "Transaction Rolled Back due to error."
        End If
    End If
    
    MsgBox "致命的なエラーが発生しました。" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Description: " & Err.Description, vbCritical, "System Error"
    Resume CleanUp
End Sub

4. リーダーからのアドバイス：なぜこの書き方なのか

コードを読み解いたか？ なぜ私がこれほどまでに特定の記述に固執しているのか、その理由を教えよう。

なぜ `adUseClient` なのか？

デフォルトの `adUseServer`（サーバーサイドカーソル）は、ネットワーク越しにサーバーのメモリを直接操作しようとする。しかし、ビューのような抽象化されたオブジェクトに対しては、サーバー側でのポインタ操作が不安定になり、エラーを誘発しやすい。`adUseClient` を指定することで、一度データをクライアント側に引き込み、そこから「変更差分」をサーバーへ投げる形になる。これが、複雑なビューを扱う際の最も安定した戦術だ。

なぜ `BeginTrans` / `CommitTrans` が必須なのか？

「ビューの更新」は、実態として「複数のテーブルへの更新」だ。ネットワークの瞬断、あるいは制約違反（Check Constraint）によって、半分だけ更新されるという事態は、データベース設計における「罪」である。トランザクションを明示的に制御することは、プロフェッショナルとしての最低限の責任だ。

なぜ `INSTEAD OF` トリガーなのか？

「Access側でSQL文（UPDATE文）を組み立てて発行すればいいじゃないか」という意見もあるだろう。確かにそれは可能だ。しかし、それは「ビューを利用する」という設計思想を放棄している。
`INSTEAD OF` トリガーを使うことで、「ビジネスロジックの隠蔽」ができる。Access側は「ビューをいじっているだけ」という単純な思考でいられ、複雑な整合性チェックや関連テーブルへの波及効果はすべてSQL Server側に任せることができる。これが、システムの保守性を劇的に高める。

結論

Access VBAからSQL Serverを操作する際、ビューは単なる「便利な表示窓」ではなく、「高度なAPIインターフェース」として扱うべきだ。

1. SQL Server側で `INSTEAD OF` トリガーを仕込み、更新ロジックをカプセル化せよ。
2. VBA側では、ADOの `adUseClient` を使い、トランザクションで鉄壁の防御を固めよ。

このアーキテクチャをマスターすれば、君の作るツールは、単なる「動く道具」から、数千、数万のデータを預けられる「堅牢なシステム」へと進化する。以上だ。業務に戻れ。