【実務・中級編】【上級者向け】SQL Serverのストアドプロシージャとカスタムフィールドを同期させる双方向通信 – Project VBA解析バイブル

スポンサーリンク
スポンサーリンク

【極限の知見】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の自動化において、単なる「データの移動」は作業に過ぎない。真のエンジニアリングとは、データの整合性を担保し、変化に強い構造を設計することにある。

ストアドプロシージャによるロジックの隠蔽、パラメータ化クエリによる安全性、そしてデータ構造に基づいた検索アルゴリズムの選択。これらを組み合わせることで初めて、あなたのツールは「現場で信頼されるシステム」へと昇華するのだ。

タイトルとURLをコピーしました