*

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

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

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

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

実行前の準備

VBAを実行する前に、

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

参照設定 - VBAProject

※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

 

 

Adsense

関連記事

データベース

【SQL Server】Management Studioを使ったトランザクションログファイルの圧縮

「ファイルが書き出せない」と問い合わせを受け、DBサーバーのマイコンピュータ確認したら、Dドライブ(

記事を読む

データベース

【SQL Server】Excel VBAでSQLを実行し、レコードを更新(追加、更新、削除)する

VBAでSQL Serverのテーブルに SQL(Insert、Update、Delete)を発行

記事を読む

データベース

【SQL Server 2012 Express】Windows Server 2012にインストールしてみる

Windows Server 2012にSQL Server 2012をインストールしてみる。 以

記事を読む

データベース

【SQL Server】エクセルをテーブルに取り込む方法

以前にManagement Studioを使ってSQLでエクセルを直接取り込む方法を書きましたが、今

記事を読む

データベース

【Management Studio】データベースの定義を取得する方法

あくまでデータベース定義の取得です。 ※データベースのデータを取得する方法ではありません。

記事を読む

Microsoft Office

【Access】クエリ実行時に「引数が違います」と表示され実行できないときの対処法

Accessのクエリを実行すると 引数が違います とポップアップが表示され、クエリが実行できない

記事を読む

データベース

【SQL Server 2012】Management Studioを使ったバックアップで面白い機能。

Management Studioを使ったバックアップをかれこれ3年くらいやってるんですが、一つのバ

記事を読む

データベース

【コマンドプロンプト】完成版!cmdでSQLの結果を変数に取得

以前、 【コマンドプロンプト】cmdでSQLの結果を変数に取得する方法 を書きましたが、

記事を読む

Microsoft Office

【Excel】グラフ(オブジェクト)の名前変更方法

Excel2013でのお話。 とりあえず、僕が探したところ三つありました。 【1つ目】名前ボ

記事を読む

Microsoft Office

【拡張子:accde】Access2007でaccdeの使い道と作成方法

うちの職場ではEUC(End User Computing)として Access2007 を多用して

記事を読む

Adsense

Message

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

Adsense

Microsoft Office
【Access】削除クエリの「指定されたテーブルから削除できませんでした。」の対処法

削除クエリで「指定されたテーブルから削除できませんでした。」と ポッ

Microsoft Office
【PowerPoint】表や図形、画像オブジェクトの位置をピッタリ合わせる方法

パワーポイントで、図形や画像等のオブジェクトの細かい位置調整は、 完

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

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

application
【Visual Studio】デバック、ブレークポイントで止まらない。

Visual Studio 2015でVB.netのプログラムをデバッ

データベース
【SQL Server】Excel VBAでSELECT文を実行してレコードセットへ取得

前回は、テーブルのレコードをVBAで直接更新(Insert/updat

→もっと見る

PAGE TOP ↑