【実務中級】Project VBAとADOを用いたSQL Serverへの進捗データ書き込み：一括更新の高速化
長年、大規模プロジェクトの統合管理基盤やレガシーな進捗管理システムの裏側を支えてきたアーキテクトなら、一度は直面する課題がある。
「MS Projectの膨大なタスクツリー（数千〜数万行）から進捗データを抽出し、SQL Serverへ同期する処理が遅すぎる」という問題だ。
逐次ループ内で `ADODB.Connection.Execute` を叩くような素朴な実装をしていないだろうか。ネットワークラウンドトリップの嵐、トランザクションの未管理、そしてVBA特有のガベージコレクションの遅延が重なれば、たった5,000タスクの同期に数十分を費やすことになる。
今回は、Project VBAのオブジェクトモデルの挙動を極限まで理解し、ADO（ActiveX Data Objects）のパラメータ化バッチ処理とトランザクション制御を駆使して、実用的な速度でSQL Serverへ進捗データを一括書き込みする手法を解説する。
—
1. Project VBAとデータベース連携のボトルネック
MS Project（WinProj）のオブジェクトモデルは、Excelのそれに比べて遥かに重い。`Project.Tasks` コレクションの走査、WBSのインデント構造、カスタムフィールド（Text1〜30, Number1〜20等）の遅延評価など、VBAからプロパティにアクセスするたびに内部でMarshallingが発生している。
さらに、外部DBへの書き込みにおいて以下のミスがパフォーマンスを致命的に劣化させる。
- 逐次INSERT/UPDATE: ループのたびにSQLを解析・実行し、RDB側のログ書き込みが発生する。
- 不適切なオブジェクト解放: COMオブジェクトやADOレコードセットを放置し、VBAのヒープ領域を圧迫する。
- エラーハンドリングの欠如: 途中エラーでデータが中途半端にコミットされ、DB側が不整合を起こす。
これらを解決するためには、「メモリ上でデータを構築し、ADO Commandのパラメータ一括バッチ（またはステートメントのバルク化）で流し込む」アプローチが必須となる。
—
2. アーキテクチャ設計：高速化の3大原則
1. 接続の局所化と明示的破棄:
ADOコネクションは必要最小限のスコープで開き、使い終わったら即座に `Close` し、変数に `Nothing` を代入してCOM参照カウンタを即時デクリメントする。
2. トランザクションの塊（バッチ処理）:
数千件のINSERT/UPDATEを1つのトランザクションで包み、RDB側のI/Oコストを最小化する。
3. Commandオブジェクトの再利用（Prepared Statement）:
SQLのパースを1回だけに抑え、パラメータの値だけを高速に差し替えて実行する。
—
3. 実装コード：SQL Server一括高速書き込みモジュール
以下のコードは、アクティブなProjectの全タスクから必要な進捗データ（ユニークID、名前、完了率、実績開始/終了日等）を抽出し、SQL Serverのテーブルへトランザクション制御下で一括書き込みを行う実用モジュールである。
Option Explicit
‘ =========================================================================
‘ ódulo名: MdlProjectSync
‘ 概要 : MS Projectの進捗データをSQL Serverへ高速一括同期する
‘ 依存 : Microsoft ActiveX Data Objects 6.x Library (ADO)
‘ =========================================================================
Public Sub SyncProgressToSQLServer()
Dim startTime As Double
startTime = Timer
‘ 1. 接続文字列の定義 (OLEDB / SQL Server Native Client / ODBC)
Const CONN_STR As String = “Provider=MSOLEDBSQL;Server=YOUR_SERVER_NAME;Database=YOUR_DB_NAME;Trusted_Connection=yes;”
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim t As Task
Dim proj As Project
Set proj = ActiveProject
‘ 2. ADO Connectionの初期化とオープン
Set conn = New ADODB.Connection
conn.CommandTimeout = 60
conn.ConnectionString = CONN_STR
On Error GoTo ErrorHandler
conn.Open
‘ 3. トランザクション開始（パフォーマンス向上とロールバック保証のため）
conn.BeginTrans
‘ 4. Commandオブジェクトの設定（パラメータ化クエリの準備）
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
‘ UPSERT (MERGE文) のSQLテンプレート定義
‘ SQL Server 2008以降のMERGE文を使用し、存在すればUpdate、無ければInsertを行う
cmd.CommandText = _
“MERGE INTO dbo.ProjectProgress AS target ” & _
“USING (SELECT ? AS ProjectUID, ? AS TaskUniqueID, ? AS TaskName, ? AS PercentComplete, ? AS ActualStart, ? AS ActualFinish) AS source ” & _
“ON target.ProjectUID = source.ProjectUID AND target.TaskUniqueID = source.TaskUniqueID ” & _
“WHEN MATCHED THEN ” & _
” UPDATE SET TaskName = source.TaskName, ” & _
” PercentComplete = source.PercentComplete, ” & _
” ActualStart = source.ActualStart, ” & _
” ActualFinish = source.ActualFinish, ” & _
” UpdatedAt = GETDATE() ” & _
“WHEN NOT MATCHED THEN ” & _
” INSERT (ProjectUID, TaskUniqueID, TaskName, PercentComplete, ActualStart, ActualFinish, CreatedAt) ” & _
” VALUES (source.ProjectUID, source.TaskUniqueID, source.TaskName, source.PercentComplete, source.ActualStart, source.ActualFinish, GETDATE());”
‘ パラメータの事前定義（型とサイズを明示することで暗黙の変換コストを排除）
Dim pProjectUID As ADODB.Parameter
Dim pTaskUID As ADODB.Parameter
Dim pTaskName As ADODB.Parameter
Dim pPercent As ADODB.Parameter
Dim pActStart As ADODB.Parameter
Dim pActFinish As ADODB.Parameter
Set pProjectUID = cmd.CreateParameter(“ProjectUID”, adVarChar, adParamInput, 50, proj.ServerID) ‘ または固定のプロジェクト識別子
Set pTaskUID = cmd.CreateParameter(“TaskUniqueID”, adInteger, adParamInput, , 0)
Set pTaskName = cmd.CreateParameter(“TaskName”, adVarWChar, adParamInput, 255, “”)
Set pPercent = cmd.CreateParameter(“PercentComplete”, adInteger, adParamInput, , 0)
Set pActStart = cmd.CreateParameter(“ActualStart”, adDate, adParamInput, , Null)
Set pActFinish = cmd.CreateParameter(“ActualFinish”, adDate, adParamInput, , Null)
cmd.Parameters.Append pProjectUID
cmd.Parameters.Append pTaskUID
cmd.Parameters.Append pTaskName
cmd.Parameters.Append pPercent
cmd.Parameters.Append pActStart
cmd.Parameters.Append pActFinish
Dim taskCount As Long
taskCount = 0
‘ 5. タスクループとパラメータバインド
Dim projUIDValue As String
projUIDValue = CStr(proj.BuiltInDocumentProperties(“项目名称”)) ‘ 代替としてファイル名等を利用可能。実運用では一意のGUIDを推奨
If projUIDValue = “” Then projUIDValue = proj.Name
For Each t In proj.Tasks
‘ 概要タスクやマイルストーン、無効なタスクのスキップ条件（必要に応じて調整）
If Not t Is Nothing Then
If t.ExternalTask = False And t.Summary = False Then
‘ パラメータ値の代入
pProjectUID.Value = projUIDValue
pTaskUID.Value = t.UniqueID
pTaskName.Value = IIf(t.Name = “”, “Unnamed Task”, t.Name)
pPercent.Value = t.PercentComplete
‘ 日付型のNullハンドリング（未着手のタスクはNA日付になるため対策）
If t.ActualStart = “NA” Then
pActStart.Value = Null
Else
pActStart.Value = CDate(t.ActualStart)
End If
If t.ActualFinish = “NA” Then
pActFinish.Value = Null
Else
pActFinish.Value = CDate(t.ActualFinish)
End If
‘ 実行
cmd.Execute
taskCount = taskCount + 1
End If
End If
Next t
‘ 6. コミット
conn.CommitTrans
MsgBox “SQL Serverへの同期が完了しました。” & vbCrLf & _
“処理件数: ” & taskCount & ” 件” & vbCrLf & _
“処理時間: ” & Format(Timer – startTime, “0.00”) & ” 秒”, vbInformation, “同期成功”
CleanUp:
‘ 7. オブジェクトの明示的解放（メモリリーク防止の要）
On Error Resume Next
If Not cmd Is Nothing Then Set cmd = Nothing
If Not conn Is Nothing Then
If conn.State = adStateOpen Then conn.Close
Set conn = Nothing
End If
Exit Sub
ErrorHandler:
‘ 異常系：ロールバック
If Not conn Is Nothing Then
If conn.State = adStateOpen Then conn.RollbackTrans
End If
MsgBox “データベース書き込みエラー番地: ” & Err.Number & vbCrLf & Err.Description, vbCritical, “致命的エラー”
Resume CleanUp
End Sub
—
4. チーフアーキテクトが教える「現場の知見」と最適化の極意
① `MERGE` 文による UPSERT の優位性
SQL Server 2008以降であれば、`INSERT` と `UPDATE` を別々に判定するロジックを書く必要はない。`MERGE` 文を使うことで、同一トランザクション内で「存在すれば更新、なければ挿入」を原子的（Atomic）に処理できる。これにより、VBA側での条件分岐コードが不要になり、ネットワークとDBの往復回数が半減する。
② ADOパラメータの型明示（型推論の排除）
`cmd.CreateParameter` を用いる際、データ型 (`adVarWChar`, `adInteger` など) とサイズを明示的に指定している。これをサボって暗黙の型変換に頼ると、SQL Server側でクエリプランのキャッシュ効率が低下し、データ型不一致による予期せぬパフォーマンス劣化や型エラーを引き起こす。特にVBAの `Variant` 型のままDBに突っ込むのは絶対に避けるべきである。
③ COMオブジェクトのライフサイクル管理とメモリ最適化
VBAには本格的なガベージコレクションが存在せず、参照カウント方式をとっている。特にループ内でADOやExcel/Projectのオブジェクトを生成・破棄する場合、スコープを抜けるまでメモリが解放されない現象が起きる。
今回はループの外で `Command` オブジェクトを1つだけ生成し、ループ内では `.Value` プロパティの書き換えだけに留めている。これが極限までメモリ消費を抑え、高速化を実現する最大の秘訣である。
④ 日付型の魔物：`”NA”` のハンドリング
MS Projectのタスクがまだ開始されていない場合、`t.ActualStart` や `t.ActualFinish` は文字列の `”NA”`（Not Applicable）を返す。これをそのまま `CDate()` にブッ込むと、容赦なく「型が一致しません (Error 13)」でマクロがクラッシュする。
コード内で行っているように、必ず `”NA”` の判定、あるいは `Null` への明示的なキャストを挟むこと。
—
5. まとめ
Project VBAと外部データベースの連携は、単なる「スクリプトの記述」ではなく、RDBのアーキテクチャとVBAのメモリモデルを双方向に理解した上でのシステム設計が求められる領域である。
今回紹介した「パラメータ化クエリの再利用」「トランザクションによる一括コミット」「明示的なオブジェクト解放」の3点を徹底すれば、数万行規模の巨大なプロジェクトスケジュールであっても、ストレスのない速度でSQL Serverへデータを流し込むことが可能になる。レガシーなシステム環境であっても、この設計思想を導入すれば寿命を劇的に延ばすことができるはずだ。現場のコードに早速組み込んでみてほしい。
