【極限の知見】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の基本、いや、業務アプリケーション開発の基本はバッチリですよ。自信を持って、次のコードを書き進めてください!
