Project VBAを掌握する極限の知見：SQL Serverと同期する極限のリソース管理エンジン
エンタープライズ環境における大規模プロジェクト管理において、Microsoft Projectのリソースシートを最新の状態に保つことは、プロジェクトの成否を分ける生命線である。しかし、人事異動、組織変更、外部パートナーの参画といった動的なリソース情報を、手動でProjectへ転記し続けるなどというのは、シニアエンジニアの自尊心が許さないナンセンスな作業だ。
今回は、ADODBを用いたSQL Serverからの高速データ抽出、COMオブジェクトのライフサイクル管理、そしてMS Project特有の「リソース一意性制約」を完全制御し、数千名規模のリソースプールを瞬時に同期する極限のリソース同期エンジンの設計思想と実装を公開する。
—
1. アーキテクチャの核心：なぜ「単純なループ処理」では破綻するのか
多くのジュニアプログラマは、リソースの同期と聞くと、SQL Serverからレコードセットを取得し、`ActiveProject.Resources` を `For Each` で回して力技で更新しようとする。
これは大規模環境において最悪のアンチパターンだ。
ProjectのCOMオブジェクトモデルは、VBA層とC++コア層の間でマーシャリングが発生するため、UIの再描画や内部インデックスの再計算を伴う操作をループ内で多発させると、処理時間が幾何級数的に増加する。さらに、COMオブジェクトの参照解放を怠れば、メモリリークを引き起こし、最終的にProjectごとクラッシュする。
我々が目指すべきは、以下の3点に最適化されたアーキテクチャである。
1. 一括取得とメモリ上でのハッシュマップ化（O(N)オーダーの実現）
2. ADODBの遅延バインディング排除と適切な接続切断
3. Projectオブジェクトの不可視化（ScreenUpdatingの完全制御）
—
2. 実装：SQL Server連携リソース同期エンジン
以下に、実務の現場で即座に稼働するプロダクション品質のVBAコードを示す。エラーハンドリング、トランザクション概念、メモリの明示的解放を完備している。
Option Explicit
‘ ==============================================================================
‘ 外部SQL Serverからリソース情報を取得し、MS Projectのリソースシートを同期する
‘ 著者: チーフアーキテクト
‘ ==============================================================================
Public Sub SyncResourcesFromSQLServer()
‘ 処理性能最大化とメモリ保護のためのフラグ退避
Dim originalScreenUpdating As Boolean
originalScreenUpdating = Application.ScreenUpdating
On Error GoTo ErrorHandler
‘ 1. UI描画とイベントの完全停止（パフォーマンス劇的改善の肝）
Application.ScreenUpdating = False
Application.DisplayAlerts = False
‘ 2. 接続文字列の定義（環境に合わせて変更すること）
Const DB_CONNECTION_STRING As String = “Provider=SQLOLEDB;Data Source=SRV-DB01;Initial Catalog=EnterprisePMO;Integrated Security=SSPI;”
Const DB_QUERY As String = “SELECT ResourceCode, ResourceName, StandardRate, MaxUnits, Email FROM vw_ActiveResources”
‘ 3. ADODBオブジェクトの宣言（早期バインディングによる型安全性の確保）
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
‘ 接続タイムアウトの設定（極限環境への配慮）
conn.ConnectionTimeout = 15
conn.CommandTimeout = 30
conn.Open DB_CONNECTION_STRING
‘ レコードセットのオープン（クライアントサイドカーソルでメモリ上に展開）
rs.CursorLocation = adUseClient
rs.Open DB_QUERY, conn, adOpenForwardOnly, adLockReadOnly
If rs.EOF Then
MsgBox “同期対象のリソースデータが取得できませんでした。”, vbExclamation, “同期エラー”
GoTo Cleanup
End If
‘ 4. 高速検索用のDictionary作成（リソースコードをキーにする）
Dim dictDBResources As Object
Set dictDBResources = CreateObject(“Scripting.Dictionary”)
Dim vData As Variant
vData = rs.GetRows() ‘ 2次元配列へ一括インポート（I/Oボトルネックの排除）
rs.Close
conn.Close
Dim i As Long
For i = 0 To UBound(vData, 2)
‘ Key: 固有コード (String), Value: 配列の列データ
Dim resKey As String
resKey = CStr(vData(0, i))
Dim resInfo(3) As Variant
resInfo(0) = vData(1, i) ‘ Name
resInfo(1) = vData(2, i) ‘ StandardRate
resInfo(2) = vData(3, i) ‘ MaxUnits
resInfo(3) = vData(4, i) ‘ Email
dictDBResources.Add resKey, resInfo
Next i
‘ 5. Project側の既存リソースとの突合・更新処理
Dim prjRes As Resource
Dim matchedKeys As Object
Set matchedKeys = CreateObject(“Scripting.Dictionary”)
For Each prjRes In ActiveProject.Resources
If Not prjRes Is Nothing Then
‘ 固有ID（ここではText1をリソースコードの格納先と仮定）をキーに突合
Dim currentCode As String
currentCode = prjRes.Text1
If dictDBResources.Exists(currentCode) Then
‘ 既存リソースの更新
Dim updatedInfo As Variant
updatedInfo = dictDBResources(currentCode)
prjRes.Name = updatedInfo(0)
prjRes.StandardRate = updatedInfo(1)
prjRes.MaxUnits = updatedInfo(2)
prjRes.EmailAddress = updatedInfo(3)
matchedKeys.Add currentCode, True
Else
‘ DB側に存在しない（退職者やアサイン解除等）場合のハンドリング
‘ ※実務ではフラグ管理や削除確認を入れることが望ましい
End If
End If
Next prjRes
‘ 6. 新規リソースの追加処理
Dim key As Variant
For Each key In dictDBResources.Keys
If Not matchedKeys.Exists(key) Then
Dim newInfo As Variant
newInfo = dictDBResources(key)
Set prjRes = ActiveProject.Resources.Add()
prjRes.Text1 = key ‘ 固有コードをText1に保持
prjRes.Name = newInfo(0)
prjRes.StandardRate = newInfo(1)
prjRes.MaxUnits = newInfo(2)
prjRes.EmailAddress = newInfo(3)
End If
Next key
MsgBox “リソースの同期が正常に完了しました。”, vbInformation, “完了”
Cleanup:
‘ 7. オブジェクトの明示的解放（メモリリークの完全防止）
On Error Resume Next
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
Set dictDBResources = Nothing
Set matchedKeys = Nothing
‘ 8. UI描画の復元
Application.ScreenUpdating = originalScreenUpdating
Application.DisplayAlerts = True
Exit Sub
ErrorHandler:
MsgBox “予期せぬエラーが発生しました: ” & Err.Description, vbCritical, “致命的エラー”
Resume Cleanup
End Sub
—
3. チーフアーキテクトが教える「極限の最適化」ポイント
上記のコードを実務環境に投入するにあたり、アーキテクトとして押さえておくべき深淵な知見を共有する。
A. `GetRows()` によるI/Oの極限圧縮
レコードセットを `Do Until rs.EOF` で1件ずつ舐めるコードは、VBAのインタープリタとCOM境界の往復が発生するため、1,000件を超えたあたりから目に見えて処理が重くなる。
`rs.GetRows()` を用いてメモリ上の2次元配列（Variant）へ一気にデータを引きずり込むことで、VBA内部のメモリ空間だけで高速にループを回すことが可能になる。これが大規模バッチ処理の基本布石である。
B. MS Projectにおける「一意性制約」のハック
Projectのリソースシートには、RDBのような厳密な主キー制約が標準では乏しい（名前の重複が許容されてしまう）。そのため、カスタムフィールドである `Text1` を「外部システム連携用リソースコード（Employee ID等）」の格納場所として強制定義し、これをコード上の主キーとしてDictionaryで突合する設計にしている。
この規約をプロジェクトテンプレートレベルで徹底させることが、システム連携を堅牢にする秘訣である。
C. COMオブジェクトのライフサイクルと解放順序
VBAのガベージコレクタは気まぐれだ。特にADODBのConnectionやRecordsetといった非マネージリソースは、スコープを抜けただけでは即座に解放されないケースがある。
必ず `State` を確認して明示的に `.Close` を呼び出し、`Set obj = Nothing` で参照カウントを確実にゼロに落とすこと。これを怠ると、バックグラウンドプロセスにゴーストコネクションが残り続け、SQL Server側のリソース枯渇を引き起こす。
—
結びにかえて
システム間連携において、VBAは「おもちゃの言語」とやゆされることがある。しかし、それは書く人間の技量とアーキテクチャの理解度が欠落している言い訳に過ぎない。
メモリのライフサイクルを支配し、データベースのI/O特性を理解した上で構築されたVBAソリューションは、専用のデスクトップアプリに匹敵する堅牢性とパフォーマンスを発揮する。
プロフェッショナルであれば、動くコードを書くだけでなく、その裏で何が起きているのかを常に意識せよ。あなたの書くコードが、組織の巨大なプロジェクトの歯車を正確に回し続けるのだから。
