真実の同期:MS ProjectカスタムフィールドとSQL ServerをADODBで結ぶ双方向通信の極致
プロジェクト管理の現場において、MS Projectは強力なツールであるが、そのデータは往々にして「孤島」となる。計画(Plan)はProjectの中にあり、実績(Actual)やマスターデータは基幹システム(SQL Server)にある。この断絶こそが、プロジェクト管理における最大の脆弱性だ。
多くの開発者は、CSVによるエクスポート・インポートという「点」の同期で妥協する。しかし、真のエンタープライズ・アーキテクチャを設計する者は、データを「線」で結び、SQL ServerをSingle Source of Truth (SSOT) とした双方向の同期エンジンを構築する。
本稿では、ADODBを用いた高効率な同期ロジック、COMオブジェクトのライフサイクル管理、そしてシステム全体の整合性を担保するための設計思想について、技術の深淵を覗き込む。
1. アーキテクチャ設計:ロジックの所在を定義せよ
双方向同期を実現する際、最も陥りやすい罠は「VBA側にビジネスロジックを書きすぎる」ことである。VBAはあくまで「運び屋(Orchestrator)」であるべきだ。
階層構造の定義
1. Data Layer (SQL Server): すべての真実が宿る場所。データの整合性チェック、重複排除、更新ロジックはすべてストアドプロシージャ内に封じ込める。
2. Integration Layer (VBA/ADODB): SQL ServerとMS Projectの橋渡し。トランザクションの制御と、オブジェクトのメモリ管理を担う。
3. Presentation Layer (MS Project): ユーザーが触れるインターフェース。カスタムフィールド(`Text1`, `Number1` 等)を、SQL Serverの属性とマッピングする。
なぜストアドプロシージャなのか? それは、ネットワーク遅延の影響を最小限に抑え、セットベース(集合論的)な処理によって、レコード単位の通信コストを劇的に削減するためである。
2. メモリ管理の極意:COMオブジェクトの解放とライフサイクル
MS Projectのオブジェクトモデルは極めて重厚であり、大規模なプロジェクトファイル(数千タスク規模)を扱う際、不適切なオブジェクトの扱いをすれば、即座にメモリリークを引き起こし、ExcelやProjectのプロセスが不安定化する。
シニアエンジニアとして心得ておくべきは、「明示的な解放」の徹底である。
' 避けるべきアンチパターン
Set rs = conn.Execute("SELECT ...")
' ...処理...
' rsをNothingにしないままループを抜ける、あるいは関数を終了する
VBAにおけるCOMオブジェクトの解放は、単なるマナーではない。それは、参照カウントを適切に減らし、OSのリソースを解放するための「生存戦略」である。
3. 実装:双方向同期エンジン
以下に、SQL Serverのストアドプロシージャを呼び出し、MS Projectのカスタムフィールドを同期させるためのコア・ロジックを示す。
3.1 SQLからProjectへの同期 (Pull Logic)
マスターデータ(例:リソースのスキルレベルや部門コード)をProject側に反映させる。
''' <summary>
''' SQL Serverからマスターデータを取得し、Projectのリソースカスタムフィールドに同期する
''' </summary>
Public Sub SyncMasterDataToProject()
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim res As MSProject.Resource
Dim connectionString As String
' 接続文字列は環境に合わせて構成(暗号化して管理すべき)
connectionString = "Provider=SQLOLEDB;Data Source=SERVER_NAME;Initial Catalog=DB_NAME;Integrated Security=SSPI;"
On Error GoTo ErrorHandler
' 1. オブジェクトの初期化
Set conn = New ADODB.Connection
Set cmd = New ADODB.Command
conn.Open connectionString
' 2. ストアドプロシージャの準備
' 引数に同期対象の期間などを渡す設計が望ましい
With cmd
.ActiveConnection = conn
.CommandText = "usp_GetResourceMaster" ' ストアド名
.CommandType = adCmdStoredProc
End With
' 3. レコードセットの取得
Set rs = cmd.Execute
' 4. 同期処理 (Projectオブジェクトへの書き込み)
' プロジェクト内のリソースをループ。ここでは高速化のためDictionary等の併用も検討せよ
For Each res In ActiveProject.Resources
If Not res Is Nothing Then
' SQLのデータとProjectのカスタムフィールド(例:Text1)を紐付け
' rsのフィールド名はSQL Serverの定義に準拠
rs.Filter "ResourceID = " & res.ID
If Not rs.EOF Then
res.Text1 = rs.Fields("DepartmentCode").Value ' 部門コード同期
res.Text2 = rs.Fields("SkillLevel").Value ' スキルレベル同期
End If
End If
Next res
MsgBox "同期が完了しました。", vbInformation
CleanUp:
' 5. 徹底したメモリ解放(重要)
' オブジェクトのライフサイクルを制御し、メモリリークを阻止する
On Error Resume Next
If Not rs Is Nothing Then
If rs.State = adStateOpen Then rs.Close
Set rs = Nothing
End If
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:
MsgBox "致命的なエラーが発生しました: " & Err.Description, vbCritical
Resume CleanUp
End Sub
3.2 ProjectからSQLへの同期 (Push Logic)
現場で更新されたカスタムフィールド(例:進捗ステータスや実稼働時間)をSQL Serverへ書き戻す。ここでは、「Upsert(Update or Insert)」の概念をストアドプロシージャ側に持たせる。
''' <summary>
''' Projectのタスク更新情報をSQL Serverへ書き戻す
''' </summary>
Public Sub PushTaskUpdatesToSQL()
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim t As MSProject.Task
Dim connectionString As String
connectionString = "Provider=SQLOLEDB;Data Source=SERVER_NAME;Initial Catalog=DB_NAME;Integrated Security=SSPI;"
On Error GoTo ErrorHandler
Set conn = New ADODB.Connection
conn.Open connectionString
' トランザクションの開始
' ネットワーク断絶によるデータの不整合を防ぐための鉄則
conn.BeginTrans
For Each t In ActiveProject.Tasks
' 完了したタスク、または特定のカスタムフィールド(Text1)にフラグがあるもののみ対象
If Not t Is Nothing Then
If t.Text1 = "SYNC_REQUIRED" Then
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = conn
.CommandText = "usp_UpdateTaskStatus" ' 更新用ストアド
.CommandType = adCmdStoredProc
' パラメータのバインド(SQLインジェクション対策および型安全性の確保)
.Parameters.Append .CreateParameter("@TaskID", adInteger, adParamInput, , t.ID)
.Parameters.Append .CreateParameter("@Status", adVarChar, adParamInput, 50, t.Text2)
.Parameters.Append .CreateParameter("@ActualWork", adDouble, adParamInput, , t.ActualWork)
.Execute
End With
' 同期済みフラグを立てる
t.Text1 = "SYNC_DONE"
End If
End If
Next t
' すべて成功した場合のみコミット
conn.CommitTrans
MsgBox "データのアップロードに成功しました。", vbInformation
CleanUp:
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 conn.RollbackTrans
MsgBox "同期に失敗しました。変更は破棄されました: " & Err.Description, vbCritical
Resume CleanUp
End Sub
4. 極限の最適化:Windows APIによるプロファイリング
大規模な同期処理において、ボトルネックが「SQLのクエリ」にあるのか、「MS Projectのオブジェクト走査」にあるのかを特定することは、アーキテクトの責務である。
VBA標準の `Timer` 関数では精度が足りない場合、Windows APIを用いてマイクロ秒単位の計測を行う。
' Windows APIによる高精度タイマーの宣言
If VBA7 Then
Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long
Else
Declare Function GetTickCount Lib "kernel32" () As Long
End If
' 使用例
Dim startTime As Long
startTime = GetTickCount()
' --- 同期処理実行 ---
Dim duration As Long
duration = GetTickCount() - startTime
Debug.Print "Execution Time: " & duration & " ms"
この計測により、タスク数が増大した際の計算量の増大($O(n)$ の挙動)を可視化し、必要に応じて「Dictionaryオブジェクトによるインメモリ・インデックス」の構築などの最適化判断を下す。
結論:システムの堅牢性は「境界」の設計に宿る
MS ProjectとSQL Serverの同期は、単なるデータのコピーではない。それは、「動的な計画」と「静的なマスター」という二つの異なる時間軸を、一つの真実へと統合するプロセスである。
1. ロジックはSQL側に寄せ、VBAは最小限に留める。
2. COMオブジェクトの解放を徹底し、メモリの整合性を守る。
3. トランザクションを活用し、中途半端な状態を許さない。
これらを守ることで、レガシーな環境であっても、現代のエンタープライズ・システムに耐えうる堅牢な自動化基盤を構築することができる。技術の真髄は、華やかな機能実装ではなく、このような地味で、しかし絶対的な「規律」にあるのだ。
