【実務・中級編】ADOでSQL ServerのIDENTITY列を取得する：新規レコード追加直後のID取得術 – Access VBA解析バイブル

【極限の設計】ADOでSQL ServerのIDENTITY列を確実に掌握する――「SELECT @@IDENTITY」の罠を回避せよ

業務システムの現場において、Access VBAをフロントエンド、SQL Serverをバックエンドに据える構成は、極めて合理的かつ強力な選択だ。しかし、この構成に踏み入れた開発者が、必ずと言っていいほど陥る「設計上の落とし穴」がある。

それが、「INSERT直後の自動採番（IDENTITY）値の取得」だ。

「`SELECT MAX(ID) FROM Table` で取ればいいじゃないか」
「`SELECT @@IDENTITY` を使えば問題ないはずだ」

もし君がそう考えているなら、今すぐその思考を捨ててほしい。それらは、マルチユーザー環境において、システムを崩壊させる「時限爆弾」を抱え込む行為に等しい。

本稿では、プロフェッショナルなアーキテクトが実践する、ADOを用いた「バグを寄せ付けない、堅牢なIDENTITY取得術」を伝授する。

1. なぜ「素人の書き方」ではシステムが死ぬのか

設計の妥当性を判断するには、まず「なぜ既存の手法が危険なのか」を論理的に理解しなければならない。

致命的な誤解①：`SELECT MAX(ID)` の脆弱性

もっとも初歩的かつ致命的なミスだ。
複数のユーザーが同時にシステムを操作している状況を想像してほしい。
1. AさんがレコードをINSERTする。
2. Aさんが `SELECT MAX(ID)` を実行しようとする。
3. そのコンマ数秒後、Bさんが別のレコードをINSERTする。
4. Aさんが受け取るのは、Bさんが作成した「自分のものではないID」になる。

これは「レースコンディション（競合状態）」と呼ばれる現象であり、データの整合性を根底から破壊する。

致命的な誤解②：`SELECT @@IDENTITY` の罠

「それなら、セッション内の最新IDを返す `@@IDENTITY` なら安全だろう」と考える中級者が多い。しかし、これもまた不完全だ。
SQL Serverにおいて、もし対象のテーブルに「トリガー」が設定されていたらどうなるか？
トリガーがログテーブルなどに別のレコードを挿入した場合、`@@IDENTITY` は「トリガーが挿入したテーブルのID」を返してしまう。君が欲しいのは親テーブルのIDなのに、全く無関係な値が返ってくる。これは、デバッグが極めて困難な、極めて質の悪いバグを生む。

2. アーキテクトの解答：`SCOPE_IDENTITY()` を使いこなせ

我々が採用すべき正解は、`SCOPE_IDENTITY()` だ。

`SCOPE_IDENTITY()` は、「現在のスコープ（実行単位）」内で生成された最新のIDENTITY値のみを返す。トリガーによって別のテーブルに挿入された値には干渉しない。これが、現在の実行コンテキストを正確に射抜く、唯一の信頼に値する手段である。

さらに、パフォーマンスと原子性（Atomicity）を極限まで高めるために、「INSERT文とSELECT文を一つのバッチとして実行する」という設計パターンを採用する。

3. プロダクション・コードの実装例

以下に、実務でそのまま利用可能な、堅牢な実装パターンを示す。単なるコードの提示ではない。オブジェクトの解放、エラーハンドリング、そしてトランザクションの概念を組み込んだ「動く部品」だ。

''
' @description: SQL Serverへレコードを挿入し、生成されたIDENTITY値を取得する
' @param  strSQL      : INSERT文 (末尾に ; SELECT SCOPE_IDENTITY() AS NewID を付与したもの)
' @param  conn        : 接続済みの ADODB.Connection オブジェクト
' @return Long        : 生成されたIDENTITY値。失敗した場合は 0
''
Public Function InsertAndGetIdentity(ByVal strSQL As String, ByRef conn As ADODB.Connection) As Long
    Dim rs As ADODB.Recordset
    Dim lngNewID As Long
    
    On Error GoTo ErrorHandler
    
    ' 1. SQL文の組み立て
    ' 実行するSQLの末尾に SCOPE_IDENTITY() を結合し、一つのバッチとして送る。
    ' これにより、ネットワーク往復（Round-trip）を最小限に抑え、かつスコープの整合性を保証する。
    Dim finalSQL As String
    finalSQL = strSQL & "; SELECT SCOPE_IDENTITY() AS NewID;"

    ' 2. Recordsetを用いた実行
    ' INSERTの結果を「値」として受け取るため、ExecuteではなくRecordsetを使用する。
    Set rs = New ADODB.Recordset
    rs.Open finalSQL, conn, adOpenStatic, adLockReadOnly

    If Not rs.EOF Then
        ' Nullチェックを行いながら値を回収
        If Not IsNull(rs!NewID) Then
            lngNewID = rs!NewID
        End If
    End If

CleanUp:
    ' 3. リソースの確実な解放 (メモリリーク防止)
    If Not rs Is Nothing Then
        If rs.State = adStateOpen Then rs.Close
        Set rs = Nothing
    End If
    InsertAndGetIdentity = lngNewID
    Exit Function

ErrorHandler:
    ' 実務ではここにログ出力処理を組み込むこと
    Debug.Print "Critical Error in InsertAndGetIdentity: " & Err.Description
    lngNewID = 0
    Resume CleanUp
End Function

' --- 使用例 ---
Public Sub Sample_Process()
    Dim cn As ADODB.Connection
    Dim lngTargetID As Long
    Dim strInsertSQL As String
    
    ' 接続文字列（環境に合わせて変更）
    Set cn = New ADODB.Connection
    cn.Open "Provider=SQLNCLI11;Data Source=YOUR_SERVER;Initial Catalog=YOUR_DB;Integrated Security=SSPI;"

    On Error GoTo TransactionError
    
    ' トランザクション開始
    cn.BeginTrans
    
    ' INSERT文の準備
    strInsertSQL = "INSERT INTO T_Orders (OrderDate, CustomerID) VALUES (GETDATE(), 101)"
    
    ' IDの取得実行
    lngTargetID = InsertAndGetIdentity(strInsertSQL, cn)
    
    If lngTargetID > 0 Then
        ' IDが取得できたので、後続の関連テーブル（明細など）への処理を行う
        Debug.Print "Successfully inserted. New ID: " & lngTargetID
        
        ' ここで明細テーブルへのINSERT処理を継続...
        ' INSERT INTO T_OrderDetails (OrderID, ItemName) VALUES (" & lngTargetID & ", 'Sample Item')"
        
        ' 全て成功したらコミット
        cn.CommitTrans
        MsgBox "処理が完了しました。ID: " & lngTargetID
    Else
        ' ID取得失敗時はロールバック
        Err.Raise vbObjectError + 513, , "IDの取得に失敗しました。"
    End If

ExitSub:
    If Not cn Is Nothing Then
        If cn.State = adStateOpen Then cn.Close
        Set cn = Nothing
    End If
    Exit Sub

TransactionError:
    If Not cn Is Nothing Then
        If cn.State = adStateOpen Then cn.RollbackTrans
    End If
    MsgBox "エラーが発生したためロールバックしました: " & Err.Description, vbCritical
    Resume ExitSub
End Sub

4. アーキテクトの視点：設計上の重要原則

このコードを使いこなす上で、以下の3点を設計指針として刻み込んでほしい。

① トランザクションの範囲を最小限かつ適切に

上記のコードでは、`InsertAndGetIdentity` の前後を `BeginTrans` と `CommitTrans` で囲んでいる。これは、「親レコードの作成」と「そのIDを用いた子レコードの作成」が、一つの不可分な論理単位（Unit of Work）であるべきだからだ。親ができたのに子が作れない、あるいはその逆という「不完全なデータ」をデータベースに存在させてはならない。

② ネットワーク・ラウンドトリップの意識

SQL文を分けて `Execute` し、その後に `SELECT` を投げるのは二流の仕事だ。ネットワーク越しに命令を2回送ることは、遅延（Latency）を2倍にすることに直結する。`INSERT …; SELECT …;` と一括で送ることで、サーバーへの通信回数を減らし、応答速度を劇的に向上させている。

③ オブジェクト・ライフサイクルの管理

VBAにおいて、`ADODB.Recordset` や `Connection` の解放漏れは、メモリリークやデータベース接続の枯渇を招く。`CleanUp` ラベルを用いた確実な `Set … = Nothing` は、保守性の高いツールを作るための最低限の規律である。

結論

`SCOPE_IDENTITY()` を用い、単一のバッチとして実行する。そして、トランザクションによって整合性を担保する。

これが、Access VBAとSQL Serverを組み合わせた業務システムにおいて、プロフェッショナルが選ぶべき「正解」だ。この設計思想をマスターすれば、君が構築するシステムは、ユーザーが増え、データが増え、時間が経過しても、決して揺らぐことのない堅牢な基盤となるだろう。