【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】Excel VBAでSELECT文を実行してレコードセットへ取得
前回は、テーブルのレコードをVBAで直接更新(Insert/update/delete) 今回
-
-
【PowerPoint】表や図形、画像オブジェクトの位置をピッタリ合わせる方法
パワーポイントで、図形や画像等のオブジェクトの細かい位置調整は、 完璧主義のこだわり派には非常にイ
-
-
【Outlook】Gmailを使う時の設定(Gmailアカウント側)
OutlookでGmailを使用するには、Gmailのアカウント側でいくつか設定をしなければいけない
-
-
【Access】リンクテーブルの一括変換。ODBCを使わずにSQL Serverへ接続
Accessのリンクテーブルの接続先を一括で変更する方法 この方法なら、ODBCを使用していな
-
-
【Outlook】開封メッセージ送信を先送りする技。
朝一でOutlookを起動したら、開封確認のメッセージ。 すごく嫌。個人的には、非常に嫌い。
-
-
【Access】クエリ実行時に「引数が違います」と表示され実行できないときの対処法
Accessのクエリを実行すると 引数が違います とポップアップが表示され、クエリが実行できない
-
-
【Access】削除クエリの「指定されたテーブルから削除できませんでした。」の対処法
削除クエリで「指定されたテーブルから削除できませんでした。」と ポップアップが表示され、クエリが実
-
-
【SQL Server】データベース作成日時(リストアした日付)を確認する方法
トラブルの調査をするとき、開発用の環境を作成して、調査するのですが… この環境っていつ作成した
-
-
【Outlook】プレビューウインドウ(閲覧ウインドウ)で表示したら、メール既読のオフ設定
前回、「Outlookの開封メッセージ送信を先送りにする方法」で、少し書きましたが、 私用のメ
-
-
【SQL Server】エクセルをテーブルに取り込む方法
以前にManagement Studioを使ってSQLでエクセルを直接取り込む方法を書きましたが、今