【極限の知見】MS ProjectとSQL Serverを同期させる:双方向通信のアーキテクチャ設計
「データをSQL Serverに飛ばすだけ」のコードを書いているなら、今すぐその手を止めてほしい。
MS Projectのカスタムフィールドをデータベースと同期させるというタスクに対し、多くの開発者は「Taskのループを回し、その中で`INSERT`や`UPDATE`文を組み立てる」という、極めて脆弱で非効率な実装を選択する。それは、データ整合性を破壊し、ネットワーク帯域を無駄に消費し、プロジェクトの規模が拡大した瞬間にパフォーマンスの壁にぶつかる、典型的な「素人の設計」だ。
我々アーキテクトが目指すべきは、「MS Projectを単なる入力インターフェース(View)として扱い、真実のソース(Source of Truth)であるSQL Serverと、ステートフルな同期エンジンを構築すること」である。
本稿では、ADODBを用いた双方向通信において、バグを寄せ付けない堅牢な設計思想と、プロダクションレベルのコード実装を伝授する。
1. 設計思想:なぜ「直接SQL」ではいけないのか
まず、設計の根幹を理解せよ。VBAから直接 `UPDATE Tasks SET …` のようなクエリを投げるのは厳禁だ。
ストアドプロシージャ(SP)による抽象化
ビジネスロジックはすべてSQL Server側に封じ込め、VBAからは「パラメータを渡してSPを叩く」というインターフェースのみを公開する。これにより、以下のメリットを享受できる。
- 原子性の確保: SP内でトランザクションを管理し、不完全なデータ更新を防ぐ。
- セキュリティ: SQLインジェクションを構造的に排除する。
- 保守性: テーブル構造が変わっても、VBA側のコードを修正する必要がない。
同期戦略:Upsert(Merge)パターンの採用
同期において最も厄介なのは、「新規追加なのか、既存データの更新なのか」の判定だ。これをVBA側で行うのは愚策である。SQL Serverの`MERGE`文を利用し、一回のコールで「存在すれば更新、なければ挿入」を完結させるのが、プロフェッショナルの流儀だ。
2. SQL Server側の実装:堅牢なデータ受皿
まずは、データの受け皿となるストアドプロシージャを定義する。ここでは、MS ProjectのTask IDをキーとし、カスタムフィールド(例:`ProjectCode`)を同期させる例を示す。
-- -----------------------------------------------------------------------------
-- Name: usp_SyncProjectTask
-- Description: MS Projectのタスク情報をUPSERTする
-- -----------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[usp_SyncProjectTask]
@TaskID INT,
@ExternalKey NVARCHAR(50), -- Project内での一意識別子
@TaskName NVARCHAR(255),
@CustomField1 NVARCHAR(100), -- Text1相当
@LastModified DATETIME
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON; -- エラー発生時に即座にロールバック
BEGIN TRANSACTION;
BEGIN TRY
MERGE INTO ProjectTaskMaster AS Target
USING (SELECT @TaskID AS TaskID, @ExternalKey AS ExternalKey) AS Source
ON (Target.ExternalKey = Source.ExternalKey)
WHEN MATCHED THEN
UPDATE SET
Target.TaskName = @TaskName,
Target.CustomField1 = @CustomField1,
Target.LastModified = @LastModified,
Target.SyncStatus = 'Updated'
WHEN NOT MATCHED THEN
INSERT (TaskID, ExternalKey, TaskName, CustomField1, LastModified, SyncStatus)
VALUES (@TaskID, @ExternalKey, @TaskName, @CustomField1, @LastModified, 'Inserted');
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
-- エラー内容を呼び出し元に伝えるための再スロー
THROW;
END CATCH
END;
3. VBA側の実装:ADODBによる高信頼通信
次に、VBA側の実装だ。ここで重要なのは、`ADODB.Command` オブジェクトを使い、パラメータ化クエリを徹底することだ。文字列連結によるクエリ構築は、このレベルのプロジェクトでは「罪」である。
以下のクラスモジュール(または標準モジュール)は、実務でそのまま利用可能な設計思想に基づいている。
Option Explicit
' =============================================================================
' Module: modProjectSync
' Description: MS ProjectとSQL Server間の同期エンジン
' =============================================================================
Private Const CONN_STR As String = "Provider=SQLOLEDB;Data Source=YOUR_SERVER;Initial Catalog=YOUR_DB;Integrated Security=SSPI;"
''' <summary>
''' MS Projectのタスク一覧をSQL Serverへ同期する
''' </summary>
Public Sub SyncTasksToSQL()
Dim dbConn As ADODB.Connection
Dim cmd As ADODB.Command
Dim t As Task
Dim errorCount As Long
' 1. オブジェクトの初期化
On Error GoTo ErrorHandler
Set dbConn = New ADODB.Connection
Set cmd = New ADODB.Command
dbConn.Open CONN_STR
' 2. Commandオブジェクトの構築(ループの外で行うのがパフォーマンスの肝)
With cmd
.ActiveConnection = dbConn
.CommandText = "usp_SyncProjectTask"
.CommandType = adCmdStoredProc
' パラメータの定義(型とサイズを厳密に指定)
.Parameters.Append .CreateParameter("@TaskID", adInteger, adParamInput, , 0)
.Parameters.Append .CreateParameter("@ExternalKey", adVarChar, adParamInput, 50, "")
.Parameters.Append .CreateParameter("@TaskName", adVarChar, adParamInput, 255, "")
.Parameters.Append .CreateParameter("@CustomField1", adVarChar, adParamInput, 100, "")
.Parameters.Append .CreateParameter("@LastModified", adDBTimeStamp, adParamInput, , Now)
End With
' 3. MS Projectオブジェクトの走査
' Application.ScreenUpdating = False ' MS Projectには存在しないが、描画負荷に注意
For Each t In ActiveProject.Tasks
' 空白タスクやサマリータスクの除外ロジックは業務要件に合わせる
If Not t Is Nothing Then
If Not t.CanEdit Then GoTo NextTask ' 編集不可タスクをスキップ
' パラメータへの値セット
cmd.Parameters("@TaskID").Value = t.ID
cmd.Parameters("@ExternalKey").Value = t.Text1 ' Text1をキーとして使用
cmd.Parameters("@TaskName").Value = t.Name
cmd.Parameters("@CustomField1").Value = t.Text2 ' Text2を同期対象とする
' 4. 実行
cmd.Execute
End If
NextTask:
Next t
MsgBox "同期が完了しました。", vbInformation
CleanUp:
If Not dbConn Is Nothing Then
If dbConn.State = adStateOpen Then dbConn.Close
End If
Set cmd = Nothing
Set dbConn = Nothing
Exit Sub
ErrorHandler:
errorCount = errorCount + 1
Debug.Print "Error on Task ID " & IIf(t Is Nothing, "Unknown", t.ID) & ": " & Err.Description
Resume Next ' 1つのエラーで全体を止めず、ログに記録して続行する堅牢な設計
End Sub
''' <summary>
''' SQL Serverから最新データを取得し、MS Projectのカスタムフィールドに反映する
''' </summary>
Public Sub SyncSQLToTasks()
Dim dbConn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim t As Task
Dim sql As String
On Error GoTo ErrorHandler
Set dbConn = New ADODB.Connection
dbConn.Open CONN_STR
' 取得クエリ(必要最小限の列のみを指定すること)
sql = "SELECT ExternalKey, CustomField1 FROM ProjectTaskMaster"
Set rs = New ADODB.Recordset
rs.Open sql, dbConn, adOpenStatic, adLockReadOnly
' MS Projectのタスクをループ
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
' Recordset内を検索(大量データの場合はDictionaryオブジェクトへの格納を推奨)
rs.MoveFirst
Do While Not rs.EOF
If rs!ExternalKey = t.Text1 Then
' 値の更新(変更がある場合のみ書き込むのがベストプラクティス)
If t.Text2 <> rs!CustomField1.Value & "" Then
t.Text2 = rs!CustomField1.Value
End If
Exit Do
End If
rs.MoveNext
Loop
End If
Next t
MsgBox "SQL Serverからのデータ反映が完了しました。", vbInformation
CleanUp:
If Not rs Is Nothing Then If rs.State = adStateOpen Then rs.Close
If Not dbConn Is Nothing Then If dbConn.State = adStateOpen Then dbConn.Close
Exit Sub
ErrorHandler:
MsgBox "同期エラー: " & Err.Description, vbCritical
Resume CleanUp
End Sub
4. アーキテクトの視点:運用上の注意点
このコードをプロダクション環境へデプロイする際、以下の3点を必ず考慮せよ。
① 検索アルゴリズムの最適化(N+1問題の回避)
上記の `SyncSQLToTasks` では、Recordsetを毎回ループさせている。これはタスク数が数百程度なら問題ないが、数千を超えると致命的に遅くなる。
解決策: 最初にSQL Serverからデータを取得し、VBAの `Scripting.Dictionary` オブジェクトに `Key = ExternalKey, Value = CustomField1` の形で格納せよ。これにより、検索コストを $O(N^2)$ から $O(N)$ へ劇的に改善できる。
② カスタムフィールドの「キー」の選定
`t.ID` をキーにするのは危険だ。MS ProjectのタスクIDは、タスクの削除や並べ替えによって変動する。必ず、プロジェクト内で一意かつ不変な値(例:`Text1` に格納された管理番号など)を `ExternalKey` として運用せよ。
③ トランザクションの粒度
本コードでは、SP内で1タスクごとにトランザクションを完結させている。これは「1つのタスクの失敗が全体に波及しない」という堅牢性を優先した設計だ。もし「全タスクが成功するか、全タスクが失敗するか」という厳密な一貫性が必要なら、VBA側で `dbConn.BeginTrans` を行い、ループ終了後に `CommitTrans` する必要がある。ただし、その場合はエラー発生時のロールバック設計を極めて慎重に行わなければならない。
結論
MS Projectの自動化において、単なる「データの移動」は作業に過ぎない。真のエンジニアリングとは、データの整合性を担保し、変化に強い構造を設計することにある。
ストアドプロシージャによるロジックの隠蔽、パラメータ化クエリによる安全性、そしてデータ構造に基づいた検索アルゴリズムの選択。これらを組み合わせることで初めて、あなたのツールは「現場で信頼されるシステム」へと昇華するのだ。
