【SQL Server】Excel VBAのレコードセットを使ってテーブルの一括更新
公開日:
:
Microsoft Office, データベース Excel, SELECT文, SQL, SQL Server, UpdateBatch, 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
Adsense
関連記事
-
-
【SQL Server 2012 Express】Windows Server 2012にインストールしてみる
Windows Server 2012にSQL Server 2012をインストールしてみる。 以
-
-
【SQL Server】Excel VBAでSELECT文を実行してレコードセットへ取得
前回は、テーブルのレコードをVBAで直接更新(Insert/update/delete) 今回
-
-
【Outlook】Gmailを使う時のOutlookの設定方法
Gmailアカウント側の設定を行った上で、Outlookの設定を行います。 Gmailアカウン
-
-
【Outlook】Gmailを使う時の設定(Gmailアカウント側)
OutlookでGmailを使用するには、Gmailのアカウント側でいくつか設定をしなければいけない
-
-
【SQL Server】varchar型、nvarchar型の文字数とバイト(byte)数を取得する
varchar型の文字数、バイト(byte)数を取得する方法 SELECT LEN(【文字
-
-
【Excel】グラフ(オブジェクト)の名前変更方法
Excel2013でのお話。 とりあえず、僕が探したところ三つありました。 【1つ目】名前ボ
-
-
【Excel】「メモリまたはディスクの空き容量が~」のポップアップで開けない時の対処法
メモリまたはディスクの空き容量が不足しているため、ドキュメントを開いたり、保存したりできません。
-
-
【Access】クエリ実行時に「引数が違います」と表示され実行できないときの対処法
Accessのクエリを実行すると 引数が違います とポップアップが表示され、クエリが実行できない
-
-
【Outlook】保存フォルダ(archive.pst)が開けない。
急に「保存フォルダ」が開けなくなった。 Outlookのバージョンは、2007のSP3。
-
-
【Excel】複数項目を指定した昇順、降順の並べ替え方法
Excel 2013でのお話。 なぜかわからないが、Excelのオートフィルターを使うと単一の