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

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

真実の同期: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. トランザクションを活用し、中途半端な状態を許さない。

これらを守ることで、レガシーな環境であっても、現代のエンタープライズ・システムに耐えうる堅牢な自動化基盤を構築することができる。技術の真髄は、華やかな機能実装ではなく、このような地味で、しかし絶対的な「規律」にあるのだ。

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