【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
関連記事
-
【Access2007】ODBCを使ってSQL Server 2012に接続
Windows7のAccess2007をデータソース(ODBC)を使って、SQL Server 20
-
【Excel】エクセルを起動(実行)しても、何も表示されない。
仕事の共有で使用しているエクセルを開くと、Excelの枠だけ表示されて何も表示されない。 壊れ
-
【SQL Server】データベース作成日時(リストアした日付)を確認する方法
トラブルの調査をするとき、開発用の環境を作成して、調査するのですが… この環境っていつ作成した
-
【Access】「更新可能なクエリであることが必要です」のクエリのエラーポップアップ
Accessでクエリを作成し、実行すると 「更新可能なクエリであることが必要です。」
-
【Outlook】開封メッセージ送信を先送りする技。
朝一でOutlookを起動したら、開封確認のメッセージ。 すごく嫌。個人的には、非常に嫌い。
-
【拡張子:accde】Access2007でaccdeの使い道と作成方法
うちの職場ではEUC(End User Computing)として Access2007 を多用して
-
【Management Studio】データベースの定義を取得する方法
あくまでデータベース定義の取得です。 ※データベースのデータを取得する方法ではありません。
-
【Outlook】同期の失敗フォルダを消す方法
問い合わせで、Outlookの「同期の失敗」フォルダを消したいと受けた。 調べてみたけど、基本
-
【レプリケーション】SQL Server 2012 Expressでレプリケーション
Windows Server 2012のSQL Server 2012 Expressを二台用意して
-
【Access】循環参照エラーの回避方法
Accessでクエリを実行しようとすると クエリ定義の SELECT で指定されている別名