【テクニカル・上級編】【プロ向け】大規模プロジェクトにおける制約条件の変更履歴をSQL Serverへ保存する監査ログシステムの構築 – Project VBA解析バイブル

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

スケジュールの「意志」を記録せよ：MS Projectにおける制約条件監査ログシステムの構築

プロジェクトマネジメントの現場において、スケジュールは単なる日付の羅列ではない。それは、リソース、制約、そしてステークホルダーの「意志」が複雑に絡み合った動的な構造体である。

しかし、多くのエンタープライズ現場で起きている悲劇は、その「意志」の変遷がブラックボックス化していることだ。ある日突然、クリティカルパスが崩壊した。原因を辿ると、誰かがタスクの制約条件を `As Soon As Possible` から `Must Start On` に書き換えていた――。この「なぜ、いつ、誰が」という問いに答えられないシステムは、もはや管理ツールとしての責務を果たしていない。

本稿では、Microsoft Project VBAを用い、タスクの制約条件（Constraint）の変更を検知し、その痕跡をSQL Serverへ永続化する、堅牢な監査ログシステムの設計・実装について詳解する。これは単なるコードの書き方ではない。オブジェクトのライフサイクルと、データの整合性を守り抜くためのアーキテクチャの講義である。

1. アーキテクチャ設計：Snapshot & Compare 戦略

MS ProjectのVBA環境には、Excelの `Worksheet_Change` のような、プロパティ変更をリアルタイムに捕捉する強力なイベントハンドラは存在しない。計算エンジン（Scheduling Engine）の内部動作に直接介入することは、COMオブジェクトの不安定化を招く極めて危険な行為だ。

したがって、我々が取るべき戦略は「Snapshot & Compare（スナップショット比較）」である。

1. Snapshot Phase: プロジェクトの現在の状態（Task ID, ConstraintType, ConstraintDate）をメモリ上の軽量な構造体に展開する。
2. Comparison Phase: ユーザーの操作後、あるいは保存直前に再度スナップショットを取得し、前回の状態との差分を抽出する。
3. Persistence Phase: 差分をSQL Serverへ、トランザクションを維持した状態で書き込む。

この手法は、MS Projectの計算エンジンを阻害せず、かつ大規模プロジェクトにおいても計算負荷を予測可能な範囲に抑えることができる。

2. データ層の定義：SQL Server Schema

監査ログの整合性を担保するため、SQL Server側には変更の「前後」を明確に記録するスキーマを用意する。

-- 監査ログテーブルの定義
CREATE TABLE ProjectAuditLogs (
    LogID BIGINT IDENTITY(1,1) PRIMARY KEY,
    ProjectName NVARCHAR(255) NOT NULL,
    TaskUID BIGINT NOT NULL,           -- MS Projectの固有識別子
    TaskName NVARCHAR(255),
    ChangedBy NVARCHAR(100),           -- Windowsユーザー名
    ChangeTimestamp DATETIME2 DEFAULT SYSDATETIME(),
    PropertyTarget NVARCHAR(50),       -- 'ConstraintType' or 'ConstraintDate'
    OldValue NVARCHAR(MAX),
    NewValue NVARCHAR(MAX),
    ClientMachine NVARCHAR(100)        -- 端末名
);

-- インデックス設計：追跡速度の最適化
CREATE INDEX IX_AuditLogs_TaskUID ON ProjectAuditLogs(TaskUID);
CREATE INDEX IX_AuditLogs_Timestamp ON ProjectAuditLogs(ChangeTimestamp);

3. 実装：高信頼性監査エンジン

ここからが本題だ。VBAの限界を突破するために、`ADODB` を用いた高速なデータ転送と、メモリリークを徹底的に排除したオブジェクト管理を実装する。

3.1. 状態保持用の構造体定義

まず、タスクの状態を保持するための軽量なクラス、あるいはユーザー定義型を定義する。ここではメモリ効率を考慮し、クラスモジュール `clsTaskState` を作成する。

' Class Module: clsTaskState
Option Explicit

Public TaskUID As Long
Public ConstraintType As Long
Public ConstraintDate As Variant ' Date型はNullを許容するためVariant

3.2. 監査エンジンのコア・ロジック

メインとなるクラス `clsAuditEngine` を構築する。ここでは、Windows APIを利用して実行ユーザーの情報を取得し、SQL Serverへの接続を最適化する。

' Class Module: clsAuditEngine
Option Explicit

' Windows API: 実行ユーザー名取得用
Private Declare PtrSafe Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" ( _
    ByVal lpBuffer As String, ByRef nSize As Long) As Long

Private m_Conn As Object ' ADODB.Connection
Private m_PreviousStates As Collection ' Dictionaryの代わりにCollectionを使用（軽量化）

Private Sub Class_Initialize()
    Set m_PreviousStates = New Collection
End Sub

Private Sub Class_Terminate()
    ' オブジェクトの明示的解放：メモリリーク防止の鉄則
    If Not m_Conn Is Nothing Then
        If m_Conn.State = 1 Then m_Conn.Close
        Set m_Conn = Nothing
    End If
    Set m_PreviousStates = Nothing
End Sub

' --- 公開メソッド ---

''' <summary>
''' 現在のプロジェクトの状態をメモリにスナップショットとして保存する
''' </summary>
Public Sub TakeSnapshot(ByRef targetApp As MSProject.Application)
    Dim t As MSProject.Task
    Dim state As clsTaskState
    
    ' 既存のコレクションをクリア
    Set m_PreviousStates = New Collection
    
    ' 計算負荷を抑えるため、ActiveProjectのタスクのみを走査
    For Each t In targetApp.ActiveProject.Tasks
        If Not t Is Nothing Then
            Set state = New clsTaskState
            state.TaskUID = t.UniqueID
            state.ConstraintType = t.ConstraintType
            state.ConstraintDate = t.ConstraintDate
            
            ' CollectionにTaskUIDをキーとして格納
            m_PreviousStates.Add state, CStr(state.TaskUID)
        End If
    Next t
End Sub

''' <summary>
''' 現在の状態とスナップショットを比較し、差分をDBへ送信する
''' </summary>
Public Sub CompareAndLog(ByRef targetApp As MSProject.Application, ByVal connectionString As String)
    Dim t As MSProject.Task
    Dim prevState As clsTaskState
    Dim userName As String
    
    userName = GetWindowsUserName()
    
    ' SQL接続の確立
    Set m_Conn = CreateObject("ADODB.Connection")
    m_Conn.ConnectionString = connectionString
    m_Conn.Open
    
    On Error GoTo ErrorHandler
    
    For Each t In targetApp.ActiveProject.Tasks
        If Not t Is Nothing Then
            ' スナップショットに存在するか確認
            On Error Resume Next
            Set prevState = m_PreviousStates(CStr(t.UniqueID))
            On Error GoTo ErrorHandler
            
            If Not prevState Is Nothing Then
                ' 制約条件（Type）の変更検知
                If t.ConstraintType <> prevState.ConstraintType Then
                    Call LogChange(t, "ConstraintType", CStr(prevState.ConstraintType), CStr(t.ConstraintType), userName)
                End If
                
                ' 制約日付（Date）の変更検知
                If t.ConstraintDate <> prevState.ConstraintDate Then
                    Call LogChange(t, "ConstraintDate", CStr(prevState.ConstraintDate), CStr(t.ConstraintDate), userName)
                End If
            End If
            Set prevState = Nothing
        End If
    Next t

CleanUp:
    If Not m_Conn Is Nothing Then
        If m_Conn.State = 1 Then m_Conn.Close
        Set m_Conn = Nothing
    End If
    Exit Sub

ErrorHandler:
    MsgBox "Audit Log Error: " & Err.Description, vbCritical
    Resume CleanUp
End Sub

' --- プライベートヘルパー ---

Private Sub LogChange(ByRef t As MSProject.Task, ByVal propName As String, ByVal oldVal As String, ByVal newVal As String, ByVal user As String)
    Dim cmd As Object
    Set cmd = CreateObject("ADODB.Command")
    
    ' パラメータ化クエリによるSQLインジェクション防止と実行計画の再利用
    With cmd
        .ActiveConnection = m_Conn
        .CommandText = "INSERT INTO ProjectAuditLogs (ProjectName, TaskUID, TaskName, ChangedBy, PropertyTarget, OldValue, NewValue) " & _
                       "VALUES (?, ?, ?, ?, ?, ?, ?)"
        .CommandType = 4 ' adCmdText
        
        ' パラメータのバインド（型を厳密に指定）
        .Parameters.Append .CreateParameter("@p1", 202, 1, 255, targetApp.ActiveProject.Name) ' adVarWChar
        .Parameters.Append .CreateParameter("@p2", 3, 1, , t.UniqueID)                     ' adBigInt
        .Parameters.Append .CreateParameter("@p3", 202, 1, 255, t.Name)                    ' adVarWChar
        .Parameters.Append .CreateParameter("@p4", 202, 1, 100, user)                      ' adVarWChar
        .Parameters.Append .CreateParameter("@p5", 202, 1, 50, propName)                   ' adVarWChar
        .Parameters.Append .CreateParameter("@p6", 202, 1, -1, oldVal)                     ' adLongVarWChar
        .Parameters.Append .CreateParameter("@p7", 202, 1, -1, newVal)                     ' adLongVarWChar
        
        .Execute
    End With
    Set cmd = Nothing
End Sub

Private Function GetWindowsUserName() As String
    Dim buf As String * 255
    Dim size As Long
    size = 255
    If GetUserName(buf, size) <> 0 Then
        GetWindowsUserName = Left(buf, size - 1)
    Else
        GetWindowsUserName = "Unknown"
    End If
End Function

4. 極限の知見：パフォーマンスと安定性のための要諦

このシステムを実戦投入するにあたり、シニアエンジニアが留意すべき「重み」が3点ある。

4.1. COMオブジェクトのライフサイクル管理

VBAにおいて、`Set t = Nothing` を怠ることは、単なるメモリ消費の問題ではない。MS Projectのメインプロセスに対して「オブジェクトがまだ使用中である」という誤ったシグナルを送り続け、アプリケーションのハングアップや、最悪の場合、計算エンジンのクラッシュを招く。`Class_Terminate` で全ての参照を明示的に切断する設計は、エンタープライズ用途では必須の作法だ。

4.2. パラメータ化クエリの必然性

`cmd.CommandText = “INSERT INTO … ‘” & val & “‘”` のような文字列連結は、初歩的なミスである。SQLインジェクションの脆弱性のみならず、`ConstraintDate` に含まれるシングルクォートや特殊文字によってクエリが破壊される。`ADODB.Command` によるパラメータバインドは、データの安全性と、SQL Server側での実行計画の再利用（パフォーマンス向上）を両立させる唯一の手段である。

4.3. スナップショットの粒度

大規模プロジェクト（タスク数 10,000超）において、全タスクのスナップショットをメモリに保持することは、メモリ消費の増大を招く。もしメモリ制約が厳しい環境であれば、`TaskUID` をキーとしたハッシュマップ（Dictionary）ではなく、外部の高速なKey-Valueストアや、一時的なローカルDB（SQLite等）を中間層として利用することを検討せよ。

結論

プロジェクトの制約条件を変更するという行為は、スケジュールの「因果律」を書き換える行為に等しい。そのログを記録することは、単なる管理業務ではなく、プロジェクトの健全性を守るための「防衛線」の構築である。

今回示したアーキテクチャは、VBAというレガシーな言語を用いながらも、現代的なエンタープライズ・データ管理の原則に基づいている。この知見を武器に、不確実なスケジュール管理の世界に、確固たる「記録」という秩序をもたらしてほしい。

スポンサーリンク
タイトルとURLをコピーしました