【SQL Server】Excel VBAのレコードセットを使ってテーブルの一括更新

前々回は読み取り専用でレコードセットへ取得し、結果をエクセルへ書き出し

前回はVBAから更新SQL(Insert、Update、Delete)を発行し、テーブルを直接更新

今回は、SELECT文でレコードセットへデータを取得し、
レコードセット内のデータ更新し、更新内容をテーブルへ一括で反映する

実行前の準備

VBAを実行する前に、

ツール／参照設定の「Microsoft ActiveX Data Objects 6.1 Library」にチェックする

※6.1じゃなくてもOK。2.0、2.1、6.0で動作することは確認済み

レコードセットへデータ取得し、
レコードを更新後にテーブルへ一括反映のサンプル

サンプルでは、取得したレコードセットに対して、レコードの追加、削除、修正後に「UpdateBatch」メソッドを使用してテーブルへ反映させている。

※接続文字列はWindows認証、SQL Server認証を環境によって使い分ける。

Option Explicit
Sub RecordSet_UpdateBatch()

    On Error GoTo ErrorProc

    Dim DBSrv As String
    Dim DBName As String
    Dim strSQL As String
    Dim rs As Recordset
    Dim strConn As String

    '----------------------------------------------------
    ' DBSrvにDBサーバ名、DBNameにデータベース名
    '----------------------------------------------------
    DBSrv = "DBSERVER\SQLEXPRESS"
    'DBSrv = "DBSERVER\SQLEXPRESS,49391" 'ポート指定付
    DBName = "db_Sales"

    '----------------------------------------------------
    ' SQL作成（更新対象テーブルのSELECT）
    '----------------------------------------------------
    strSQL = "SELECT * FROM [Work_Table]"
    
    '----------------------------------------------------
    ' 接続文字列の指定
    '----------------------------------------------------
    'Windows認証
    strConn = "Provider=SQLOLEDB;Data Source='" & DBSrv & "';Initial Catalog='" & DBName & "';Trusted_Connection=Yes"
    'SQL Server認証
    'strConn = "Provider=SQLOLEDB;Data Source='" & DBSrv & "';Initial Catalog='" & DBName & "';UID=【ユーザ名】;PWD=【パスワード】;"

    'オブジェクト生成
    Set rs = New ADODB.Recordset

    'バッチ更新モードでレコードセットをオープン
    rs.Open strSQL, strConn, adOpenKeyset, adLockBatchOptimistic, adCmdText
    
    '----------------------------------------------------
    ' レコードの追加
    '----------------------------------------------------
    rs.AddNew
        rs.Fields("ID").Value = "00016"
        rs.Fields("ユーザ名").Value = "VBA_レコードセットで追加"
        rs.Fields("日時").Value = Now

    '----------------------------------------------------
    ' レコードの削除
    '----------------------------------------------------
    rs.MoveFirst
    Do Until rs.EOF

        If rs.Fields("ID").Value = "00001" Then
            rs.Delete
        End If
        rs.MoveNext
    Loop
 
    '----------------------------------------------------
    ' レコードの修正
    '----------------------------------------------------
    rs.MoveFirst
    Do Until rs.EOF

        If rs.Fields("ID").Value = "00015" Then
            rs.Fields("ユーザ名").Value = "VBA_レコードセットで更新"
        End If
        
        rs.MoveNext
    Loop
 
    '----------------------------------------------------
    ' DB更新(上記のレコードの追加、削除、修正をDBに反映)
    '----------------------------------------------------
    rs.UpdateBatch
 
    'クローズ
    rs.Close
    Set rs = Nothing

    Exit Sub

'エラー処理
ErrorProc:

    MsgBox Err.Number & vbCrLf & Err.Description

End Sub

検証環境
Excel 2007 or Excel 2013
SQL Server 2012