【極限のAccess VBA】ネットワークの重圧から解放される「Disconnected Recordset」による爆速・オフライン編集アーキテクチャ
「Accessの動作が、ネットワーク越しだとどうしても重くなる……」
「複数人が同時に使うと、データベースのロックが頻発してエラーが出る……」
もしあなたが、マクロの記録から一歩踏み出し、実務で使える「プロの道具」を作ろうとしているなら、必ずこの壁にぶつかるはずです。
こんにちは。今日は、Access VBAの世界において、初心者と上級者を分ける決定的な境界線の一つである「Disconnected Recordset(切断型レコードセット)」という魔法についてお話ししましょう。
これは単なるテクニックではありません。データベースとアプリケーションの「付き合い方」を変える、アーキテクチャ(設計思想)の話です。ここをマスターすれば、あなたの作るシステムは劇的に軽く、そして強靭なものになりますよ。
1. なぜ「繋ぎっぱなし」ではいけないのか?(DAOとADOの思想の違い)
まず、私たちが普段使っている「データの扱い方」を整理しましょう。Access VBAには大きく分けて2つの流派があります。
DAO (Data Access Objects)
Accessのエンジン(ACE/Jet)に最も近い、いわば「純正の職人」です。Access内のテーブルを操作するなら、これほど速いものはありません。しかし、DAOは基本的に「データベースとの接続を維持しながら」操作を行うことを前提としています。
ADO (ActiveX Data Objects)
こちらは「世界中のデータベースと話せる外交官」です。SQL ServerやOracleといった外部サーバーとも通信できます。そして、ADOの真骨頂が、今回扱う「Disconnected(切断型)」という概念です。
「繋ぎっぱなし」の限界
一般的なプログラムは、データを読み書きする間、ずっとデータベースとの「通信路(コネクション)」を確保し続けます。これを「Connected(接続型)」と呼びます。
しかし、ネットワーク越しにこの通信路を維持し続けると、以下のような問題が発生します。
1. ネットワーク負荷: 1行書き換えるたびに、ネットワークを「トントン」と叩いて通信が発生する。
2. ロックの競合: 通信路を開いている間、他のユーザーがそのデータに触れなくなる(ロック待ち)。
3. 不安定さ: ほんの一瞬のネットワーク断絶で、プログラムがクラッシュする。
これを解決するのが、「一度データをメモリに全部持ってきて、通信を切断し、手元で作業してから、最後にまとめて送りつける」という手法です。
2. Disconnected Recordsetのイメージ図
イメージしてみてください。
- Connected(接続型): スーパーのレジにずっと並び、商品(データ)を1個取るたびに店員さんに支払い、また次の商品を取りに戻る。これでは行列ができませんよね?
- Disconnected(切断型): スーパーで「買い物リスト(Recordset)」を手に取り、レジを通らずに店内を歩き回って、メモに「これとこれが欲しい」と書き込む。最後にまとめてレジへ行き、一括で会計する。
これが、Disconnected Recordsetのアーキテクチャです。
3. 実装の極意:オフライン編集の実践コード
それでは、実際にどのようにコードを書くのか見ていきましょう。ここでの最大のポイントは、`CursorLocation` プロパティを `adUseClient` に設定することです。これを行わないと、メモリ上での独立した作業はできません。
' ※参照設定で "Microsoft ActiveX Data Objects x.x Library" にチェックを入れてください
Option Compare Database
Option Explicit
Sub DisconnectedUpdateExample()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConn As String
Dim targetID As Long
' 接続文字列(環境に合わせて変更してください)
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & CurrentProject.FullName & ";"
On Error GoTo ErrorHandler
' 1. 【準備】データベースへの接続
Set conn = New ADODB.Connection
conn.Open strConn
' 2. 【重要】Recordsetの生成と「クライアントサイドカーソル」の設定
' これにより、データがメモリ上にコピーされ、DBとの接続を切っても操作可能になります
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
' 3. 【取得】データをメモリへロード
' adOpenStatic と adLockOptimistic を組み合わせるのが定石です
rs.Open "SELECT ID, ProductName, Price FROM Products", conn, adOpenStatic, adLockOptimistic
' --- ここで一度、DBとの接続を切断しても良い(今回は例として接続したまま進めます) ---
' conn.Close
' --------------------------------------------------------------------------
' 4. 【編集】メモリ上での操作(ネットワーク負荷はゼロ!)
If Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF
' 例:価格を10%アップさせるという加工を行う
rs!Price = rs!Price * 1.1
' 変更をメモリ上のレコードセットに記録(まだDBには反映されません)
rs.Update
rs.MoveNext
Loop
End If
' 5. 【反映】一括更新(バッチ更新)
' ここで初めて、ネットワークを通じて「ドカン!」と変更分だけが送信されます
Debug.Print "更新を開始します..."
rs.UpdateBatch ' メモリ上の変更をDBへ一括適用
Debug.Print "更新が完了しました。"
CleanUp:
' 後片付け(これを忘れるとメモリリークの原因になります)
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 Sub
ErrorHandler:
MsgBox "エラーが発生しました: " & Err.Description, vbCritical, "システムエラー"
Resume CleanUp
End Sub
コードの重要ポイント解説
1. `rs.CursorLocation = adUseClient`:
これが「魔法の呪文」です。これを書かないと、Recordsetはサーバー(DBエンジン)側で動こうとし、接続を切った瞬間に死んでしまいます。`adUseClient` とすることで、データがあなたのPCのメモリ(クライアント側)に展開されます。
2. `rs.Update` vs `rs.UpdateBatch`:
- `rs.Update`: 「この行の編集が終わったよ」というメモリ内での確定。
- `rs.UpdateBatch`: 「メモリに溜まった変更を、全部まとめてDBに書き込んで!」という命令。
3. `adOpenStatic`:
データを静的に(スナップショットとして)取得するための設定です。オフライン編集にはこの安定性が不可欠です。
4. 陥りやすい罠:プロが教える注意点
この手法は強力ですが、無敵ではありません。以下の3点には特に注意してください。
① コンフリクト(衝突)の発生
あなたがオフラインで編集している間に、別のユーザーが同じレコードを書き換えてしまった場合、`UpdateBatch` を実行した瞬間に「データが変更されています」というエラーが発生します。
- 対策: 非常に重要なデータの場合、更新前に「最終更新日時」をチェックするなどのロジックを検討しましょう。
② メモリ消費量
数万件、数十万件という膨大なデータを一度に `adUseClient` で読み込むと、あなたのPCのメモリを食いつぶし、Accessが「応答なし」になります。
- 対策: 「必要な分だけ(WHERE句で絞り込む)」を徹底してください。オフライン編集は「小回りの利く塊」に対して使うのが最も効果的です。
③ 参照設定の忘れ
ADOを使うには、VBAエディタの [ツール] -> [参照設定] から `Microsoft ActiveX Data Objects x.x Library` にチェックを入れる必要があります。これがないと、`ADODB.Recordset` という言葉自体が理解されず、コンパイルエラーになります。
まとめ:一歩先のエンジニアへ
「繋ぎっぱなし」のDAOスタイルは、ローカルなAccess開発では正解です。しかし、あなたが「ネットワーク越しでもサクサク動き、多くのユーザーにストレスを与えないシステム」を作りたいと願うなら、このADOによるDisconnectedアーキテクチャは、最強の武器になります。
「データをメモリに抱え込み、一気に叩き込む」
この感覚を掴めたとき、あなたのVBAスキルは、単なる「マクロ使い」から「システムアーキテクト」へと進化しているはずです。
ここをクリアすれば、Access VBAの基本はバッチリですよ。さあ、次のステップへ進みましょう!
