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

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

【極限の知見】SQL ServerとMS Projectを「同期」させる――双方向通信で実現する真の一元管理術

こんにちは。現場の自動化を司るアーキテクトです。

「マクロの記録」から一歩踏み出し、「自分だけのツール」を作ろうとしているあなた。素晴らしい挑戦です。しかし、現場で本当に求められるのは、単に「ボタンを押したら何かが動く」ツールではありません。「データが常に正しい状態に保たれている」という、信頼性の高いシステムです。

プロジェクト管理において、最も恐ろしいのは「MS Project上の計画」と「社内のマスターデータ(予算、リソース単価、プロジェクトコードなど)」が乖離してしまうことです。

今回は、その乖離を根絶するための究極の手法、「SQL ServerのストアドプロシージャとMS Projectのカスタムフィールドを同期させる双方向通信」を解説します。少し高度な内容に見えるかもしれませんが、大丈夫。一歩ずつ、本質を紐解いていきましょう。ここをクリアすれば、あなたのVBAスキルは「事務作業の自動化」から「業務システムの構築」へと昇華します。

1. なぜ「ストアドプロシージャ」を介するのか?

通常、VBAからデータベースを操作する場合、`SELECT * FROM Table` のような単純なクエリを投げがちです。しかし、プロフェッショナルの設計は違います。私たちはストアドプロシージャ(Stored Procedure)を呼び出します。

なぜ直接テーブルを触らないのか?

1. カプセル化(安全性): VBA側に「どのテーブルのどの列を更新するか」という詳細を知らせません。VBAは「このデータを渡すから、適切に処理してくれ」と命令するだけです。
2. パフォーマンス: 複雑な計算や複数のテーブルへの更新を、サーバー側で一括処理できます。ネットワークの往復回数を減らすことは、大規模データにおいて決定的な差を生みます。
3. 一貫性: データの整合性チェック(例:予算がマイナスにならないか等)をSQL側で強制できるため、不完全なデータが入り込むのを防げます。

2. 設計図:双方向同期のメカニズム

今回のシステムは、以下の2つのルートで動きます。

  • ルートA:SQL $\to$ Project(マスター情報の反映)

SQL Serverにある最新の「リソース単価」や「プロジェクト属性」を、Projectのカスタムフィールド(`Text1`や`Number1`など)へ流し込む。

  • ルートB:Project $\to$ SQL(計画情報の書き戻し)

Project上で調整した「予定工数」や「進捗率」を、SQL Serverの管理テーブルへ書き戻す。

3. 実装フェーズ:SQL Server側の準備

まずは、データの入り口となるストアドプロシージャを作成します。ここでは、Projectからの更新を受け取るための簡単な例を示します。

-- プロジェクト情報のマスターテーブル
CREATE TABLE ProjectMaster (
    ProjectID NVARCHAR(50) PRIMARY KEY,
    ProjectName NVARCHAR(200),
    Budget DECIMAL(18, 2),
    LastUpdated DATETIME
);

-- Projectからの更新を受け取るストアドプロシージャ
CREATE PROCEDURE usp_SyncProjectFromVBA
    @ProjID NVARCHAR(50),
    @Budget DECIMAL(18, 2)
AS
BEGIN
    SET NOCOUNT ON;
    
    -- データの更新(存在すればUpdate、なければInsertするUPSERT処理)
    MERGE ProjectMaster AS target
    USING (SELECT @ProjID, @Budget) AS source (ProjectID, Budget)
    ON (target.ProjectID = source.ProjectID)
    WHEN MATCHED THEN
        UPDATE SET Budget = source.Budget, LastUpdated = GETDATE()
    WHEN NOT MATCHED THEN
        INSERT (ProjectID, Budget, LastUpdated)
        VALUES (@ProjID, @Budget, GETDATE());
END;

4. 実装フェーズ:Project VBAによる制御

次に、VBAからこのプロシージャを叩くコードを書きましょう。
重要: VBAの参照設定で `Microsoft ActiveX Data Objects x.x Library` にチェックを入れておいてください。

双方向通信の実装コード

Option Explicit

' =================================================================
' 構成要素: ADODBを用いたSQL Serverとの双方向同期モジュール
' 著: 伝説のチーフアーキテクト
' =================================================================

' 接続文字列(環境に合わせて書き換えてください)
Private Const CONN_STR As String = "Provider=SQLOLEDB;Data Source=YOUR_SERVER;Initial Catalog=YOUR_DB;Integrated Security=SSPI;"

''' <summary>
''' 【ルートA】SQL Serverから最新の予算情報を取得し、Projectのカスタムフィールドに反映する
''' </summary>
Public Sub SyncMasterToProject()
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim t As Task
    Dim projID As String
    
    On Error GoTo ErrorHandler
    
    Set conn = New ADODB.Connection
    conn.Open CONN_STR
    
    ' SQLからプロジェクト情報を取得(ここでは単純化のため全件取得)
    Set rs = New ADODB.Recordset
    rs.Open "SELECT ProjectID, Budget FROM ProjectMaster", conn, adOpenStatic, adLockReadOnly
    
    ' Projectのタスクをループして、IDが一致するものを更新
    ' ※ProjectのText1にProjectIDが入っている前提
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            projID = t.Text1 ' カスタムフィールドText1にProjectIDを格納している想定
            
            ' レコードセットを検索
            rs.MoveFirst
            Do Until rs.EOF
                If rs!ProjectID = projID Then
                    ' 一致したらカスタムフィールドNumber1に予算を書き込む
                    t.Number1 = rs!Budget
                    Exit Do
                End If
                rs.MoveNext
            Loop
        End If
    Next t
    
    MsgBox "マスター情報の同期が完了しました。", vbInformation
    
CleanUp:
    ' オブジェクトのライフサイクル管理:必ず解放する
    If Not rs Is Nothing Then If rs.State = adStateOpen Then rs.Close: Set rs = Nothing
    If Not conn Is Nothing Then If conn.State = adStateOpen Then conn.Close: Set conn = Nothing
    Exit Sub

ErrorHandler:
    MsgMsg = "エラーが発生しました: " & Err.Description
    MsgBox MsgMsg, vbCritical
    Resume CleanUp
End Sub

''' <summary>
''' 【ルートB】Project上の予算変更をSQL Serverへ書き戻す
''' </summary>
Public Sub SyncProjectToMaster()
    Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim t As Task
    Dim projID As String
    Dim newBudget As Double
    
    On Error GoTo ErrorHandler
    
    Set conn = New ADODB.Connection
    conn.Open CONN_STR
    
    ' 選択されているタスク、または特定の条件のタスクを対象にする
    ' ここでは例として、すべてのタスクをループ
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            projID = t.Text1
            newBudget = t.Number1 ' カスタムフィールドNumber1から予算を取得
            
            If projID <> "" Then
                ' ストアドプロシージャの呼び出し準備
                Set cmd = New ADODB.Command
                With cmd
                    .ActiveConnection = conn
                    .CommandText = "usp_SyncProjectFromVBA" ' 呼び出すプロシージャ名
                    .CommandType = adCmdStoredProc
                    
                    ' パラメータの定義(型と方向を厳密に指定するのがプロの技)
                    .Parameters.Append .CreateParameter("@ProjID", adVarChar, adParamInput, 50, projID)
                    .Parameters.Append .CreateParameter("@Budget", adDecimal, adParamInput, , newBudget)
                    
                    ' 実行
                    .Execute
                End With
                Set cmd = Nothing
            End If
        End If
    Next t
    
    MsgBox "SQL Serverへの書き戻しが完了しました。", vbInformation

CleanUp:
    If Not conn Is Nothing Then If conn.State = adStateOpen Then conn.Close: Set conn = Nothing
    Exit Sub

ErrorHandler:
    MsgBox "同期エラー: " & Err.Description, vbCritical
    Resume CleanUp
End Sub

5. アーキテクトの視点:陥りやすい罠とパフォーマンスの極意

ここからは、リファレンスには載っていない「現場の知恵」を伝授します。

① オブジェクトのライフサイクルを意識せよ

コード内の `CleanUp` セクションを見てください。`Set rs = Nothing` や `conn.Close` を忘れていませんか?
データベース接続は「リソース」です。これを解放し忘れると、サーバー側の接続上限に達し、システム全体を停止させる「接続リーク」を引き起こします。「開けたら閉める」は鉄則です。

② ループ内でのCommandオブジェクト生成の是非

上記のコードでは分かりやすさのためにループ内で `Set cmd = New ADODB.Command` を行っていますが、数千、数万のタスクを扱う場合、これはパフォーマンスを低下させます。
真のプロは、ループの外で `Command` オブジェクトを一つだけ生成し、ループ内では `.Parameters(0).Value = …` のように値だけを書き換えて再利用します。 これにより、メモリ確保のオーバーヘッドを劇的に削減できます。

③ カスタムフィールドの「型」の不一致

Projectの `Text1` は文字列ですが、SQL Serverの `DECIMAL` にそのまま入れようとすると型変換エラーで落ちます。VBA側で `CDbl()` や `CStr()` を使い、データ型を明示的に制御する癖をつけてください。

まとめ:次は「自動化の先」へ

いかがでしたか?
単なる「値のコピー」ではなく、「ストアドプロシージャを介した、型を意識した双方向通信」……。これができるようになった時、あなたは単なるマクロ使いではなく、「データ駆動型システム」の設計者への階段を登っています。

最初はエラーが出るかもしれません。接続文字列が間違っていたり、SQLの権限が足りなかったり。でも、そのエラーこそが、システムを堅牢にするための最高の教科書です。

ここをクリアすれば、Project VBAの基本、いや、業務アプリケーション開発の基本はバッチリですよ。自信を持って、次のコードを書き進めてください!

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