「さっき入れたデータ、IDは何番？」を卒業する。ADOでSQL ServerのIDENTITY列をスマートに取得する極意
こんにちは。開発の現場へようこそ。
マクロの記録から一歩踏み出し、「自分でプログラムを制御したい」と考え始めたあなた。その意欲こそが、一流のエンジニアへの第一歩です。
Access VBAを使っていて、こんな壁にぶつかったことはありませんか？
「SQL Serverにデータを追加（INSERT）した直後に、そのレコードに自動で割り振られた『ID（連番）』を知りたい。でも、どうやって取得すればいいんだろう？」
「`MAX(ID)` で最新の番号を取ろうとしたら、他のユーザーが同時にデータを追加したせいで、違う人のIDを拾ってしまった……」
大丈夫、安心してください。これは、Accessからサーバー（SQL Server）を操作する際に、誰もが一度は通る「登竜門」のようなものです。今日は、この問題を「プロのやり方」で鮮やかに解決する方法を伝授します。
ここをクリアすれば、あなたのVBAは「ただ動くコード」から「信頼されるシステム」へと進化しますよ。
1. なぜ「MAX(ID)」ではいけないのか？（設計の落とし穴）
まず、初心者が陥りがちな「間違ったアプローチ」を理解しておきましょう。
最も直感的な方法は、「データを追加した後、テーブルのID列の最大値（MAX）を検索する」というものです。
-- やってはいけない例
INSERT INTO T_顧客 (顧客名) VALUES ('田中太郎');
SELECT MAX(顧客ID) FROM T_顧客; -- これが危ない！一見、正しそうに見えますよね？ しかし、マルチユーザー（複数の人が同時に使う）環境では、このコードは「爆弾」になります。
【何が起きるのか？】
1. あなたが「田中太郎」さんを登録する。
2. （コンマ数秒の間に、別の人が「鈴木次郎」さんを登録する）
3. あなたが `MAX(顧客ID)` を取得しようとする。
4. 結果として、あなたのデータではなく、後から入った「鈴木さん」のIDが返ってくる。
これを「レースコンディション（競合状態）」と呼びます。データの整合性が崩れ、システムは大混乱に陥ります。私たちは、「自分が今、まさに作ったレコードのID」を、他の誰にも邪魔されずに特定しなければなりません。
2. 武器を選ぼう：DAOではなく「ADO」を使う理由
Accessには、データを扱うための道具が主に2つあります。
- DAO (Data Access Objects): Access内部のテーブル（.accdb）を操作するのが得意な、地元の名士。
- ADO (ActiveX Data Objects): SQL Serverなどの外部データベースと高速に通信するための、世界的なエージェント。
今回のようにSQL Serverを相手にする場合、ADOを使うのが鉄則です。ADOは、サーバー側で実行した結果をダイレクトに受け取る能力に長けているからです。
3. 解決策：`SCOPE_IDENTITY()` という魔法の呪文
SQL Serverには、私たちが求めている「正解」が用意されています。それが `SCOPE_IDENTITY()` という関数です。
これは、「現在の接続（スコープ）内で、最後に生成されたIDENTITY値（自動採番された値）」を返してくれる、非常に安全な関数です。これを使えば、他の人が同時にデータを追加しても、混ざることはありません。
さらに、スマートな書き方として、「INSERT文」と「SELECT文」を一度の命令としてまとめて送る手法をとります。これが、プロのアーキテクチャです。
4. 実践：最強のID取得パターン（コード例）
それでは、実際に現場でそのまま使えるコードを見てみましょう。
このコードは、データを追加し、その直後にIDを受け取り、一連の処理を「トランザクション（一塊の作業）」として管理する、非常に堅牢な設計になっています。
''' <summary>
''' SQL Serverへ顧客を登録し、生成されたIDを返却する
''' </summary>
''' <param name="customerName">登録する顧客名</param>
''' <returns>生成された顧客ID</returns>
Public Function AddCustomerAndGetID(ByVal customerName As String) As Long
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConn As String
Dim strSQL As String
Dim newID As Long
' 1. 接続文字列の設定 (環境に合わせて書き換えてください)
strConn = "Provider=SQLOLEDB;Data Source=サーバー名;Initial Catalog=DB名;User ID=ユーザーID;Password=パスワード;"
' 2. SQL文の構築
' 重要：INSERT文の直後に、SCOPE_IDENTITY()を実行する命令をセミコロンで繋ぎます
' これにより、1回の通信で「追加」と「ID取得」を完結させます
strSQL = "INSERT INTO T_顧客 (顧客名) VALUES ('" & customerName & "'); " & _
"SELECT SCOPE_IDENTITY() AS NewID;"
On Error GoTo ErrorHandler
' 3. オブジェクトの生成と接続
Set conn = New ADODB.Connection
conn.Open strConn
' トランザクションの開始（失敗したら全て取り消せるようにする）
conn.BeginTrans
' 4. SQLの実行
' Recordsetを使って、SELECTの結果（新しいID）を受け取ります
Set rs = New ADODB.Recordset
rs.Open strSQL, conn, adOpenStatic, adLockReadOnly
' 5. 結果の取り出し
If Not rs.EOF Then
newID = rs.Fields("NewID").Value
End If
' 6. コミット（確定）
conn.CommitTrans
' 戻り値の設定
AddCustomerAndGetID = newID
CleanUp:
' 7. 後始末（オブジェクトの解放はエンジニアの嗜みです）
If Not rs Is Nothing Then
If rs.State = adStateOpen Then rs.Close
Set rs = Nothing
End If
If Not conn Is Nothing Then
If conn.State = adStateOpen Then conn.Close
Set conn = Nothing
End If
Exit Function
ErrorHandler:
' エラーが発生した場合はロールバック（追加作業を無かったことにする）
If Not conn Is Nothing Then
If conn.State = adStateOpen Then conn.RollbackTrans
End If
MsgBox "エラーが発生しました: " & Err.Description, vbCritical, "システムエラー"
AddCustomerAndGetID = 0
Resume CleanUp
End Functionここがプロのポイント！
1. SQLの結合 (`INSERT…; SELECT…;`):
命令を2回に分けず、1回の「バッチ」として送っています。これにより、ネットワークの往復（ラウンドトリップ）が減り、パフォーマンスが向上します。
2. `SCOPE_IDENTITY()` の使用:
他のユーザーの操作に影響されない、最も安全なID取得方法です。
3. トランザクション (`BeginTrans` / `CommitTrans`):
「データの追加には成功したけれど、その後のID取得に失敗した」という中途半端な状態を防ぎます。失敗したら `RollbackTrans` で、まるで何もなかったかのように元に戻します。
4. オブジェクトのライフサイクル管理:
`Set rs = Nothing` など、使い終わったオブジェクトを明示的に解放しています。これを怠ると、メモリリーク（メモリの無駄遣い）の原因になります。
5. まとめ：一段上のエンジニアへ
いかがでしたでしょうか？
「データを追加してIDをもらう」という一見単純な動作の中にも、「同時実行性の確保」「ネットワーク効率」「データの整合性」という、エンジニアリングの重要なエッセンスが詰まっています。
- MAX(ID) は使わない。
- `SCOPE_IDENTITY()` を使う。
- ADOで、INSERTとSELECTをセットで投げる。
- トランザクションで守る。
この4点を守るだけで、あなたの作るシステムは劇的に安定します。
最初は難しく感じるかもしれませんが、一度このパターンを自分のものにしてしまえば、もう迷うことはありません。一歩ずつ、確実に、コードの深淵へと進んでいきましょう。
応援していますよ！
