本記事では、マクロからSQL Serverのデータを更新する方法についてご説明します。
マクロからSQL Serverのデータを更新する方法
マクロからSQL Serverのデータを更新するには、次の流れの通りにコードを書いていきます。
このインスタンスは、マクロがSQL Serverに接続するために必要です。
このインスタンスは、SQL Serverのデータの更新に必要です。
この設定により、Recordsetオブジェクトを開いたり操作することができるようになります。
テーブルのデータ更新は、RecordsetオブジェクトのOpenメソッドで行います。
コードの例
Excelのマクロのコード(例)
Excelのマクロのコード(例)は次の通りです。
Dim DBName As String, connDB As String Dim oCon As ADODB.Connection Dim oRS As ADODB.Recordset 'DBの名前 DBName = "workDB" 'データベース接続情報を取得 connDB = "Provider=SQLNCLI11.1;" connDB = connDB & "Data Source=(LocalDB)\MSSQLLocalDB;" connDB = connDB & "Initial Catalog=" & DBName & ";" connDB = connDB & "Trusted_Connection=yes;" 'Connectionオブジェクトのインスタンスを生成する Set oCon = New ADODB.Connection 'SQL Serverに接続する oCon.Open connDB 'Recordsetオブジェクトのインスタンスを生成する Set oRS = New ADODB.Recordset '先ほど取得したデータベース接続情報が設定されているoConをActiveConnectionプロパティに設定し、 'ConnectionオブジェクトとRecordsetオブジェクトを関連付ける '⇒Recordsetオブジェクトを開いたり操作するのに必要な設定 oRS.ActiveConnection = oCon 'データを更新するSQL文をSourceプロパティに設定する sqlStr = "Update dbo.tbl_userInfo set " sqlStr = sqlStr & "memName = N'" & "ユーザ1" & "'" sqlStr = sqlStr & ", postalCode = '" & "123-4567" & "'" sqlStr = sqlStr & ", addrs = N'" & "住所1-1-1" & "'" sqlStr = sqlStr & ", phoneNum = '" & "080-1234-5678" & "'" sqlStr = sqlStr & ", birth = '" & 19870511 & "'" sqlStr = sqlStr & ", mailaddrs = '" & "abc@xyz.com" & "'" sqlStr = sqlStr & " WHERE memID = " & 1 oRS.Source = sqlStr '指定したテーブルのデータを更新する oRS.Open '各終了処理 oRS.Close oCon.Close If Not oRS Is Nothing Then Set oRS = Nothing If Not oCon Is Nothing Then Set oCon = Nothing
コード(例)の29行目から36行目で記述したSQL(Update)文は次の通りです。
Update dbo.tbl_userInfo set
memName = N’ユーザ1′
, postalCode = ‘123-4567′
, addrs = N’住所1-1-1’
, phoneNum = ‘080-1234-5678’
, birth = ‘19870511’
, mailaddrs = ‘abc@xyz.com’
WHERE memID = 1
コード(例)だとUpdate文が分かりにくいと思ったので参考にしていただければと思います。
コードの解説
最初に見て頂きたいのは40行目です。
RecordsetオブジェクトのOpenメソッドを使い、テーブルのデータを更新しています。
なお、更新するデータはSQL文で記述し、Sourceプロパティに指定します。(30行目から37行目)
また、SQL Serverのデータを更新するにはまずSQL Serverに接続しなければいけませんが、マクロからSQL Serverに接続する処理(10行目から19行目)については次の記事で詳しく説明しているので、そちらもあわせてご覧ください。
【ExcelVBA】マクロからSQL Serverに接続するには文字化けに注意
Excelのマクロのコード(例)の30行目と32行目で「N」の文字が使われているのが分かりますでしょうか?
この「N」がないと、データの更新処理が行われると全角文字が「?」に文字化けします。
この「N」の文字は、データを更新する際に全角文字が文字化けないようにするのに必要な文字です。(半角文字や数値は文字化けしないので必要はありません)
なので、全角文字のデータで更新する場合は「N」の文字を忘れないようにしましょう。
【参考:文字化けしたデータの例(画像)】
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、先ほどのコードの2行目の「ADODB.Connection」と3行目の「ADODB.Recordset」というオブジェクトが、「msado28.tlb」というファイルを参照するからです。
Dim oCon As ADODB.Connection Dim oRS As ADODB.Recordset
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」と「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
【動画】マクロからSQL Serverのデータを更新する参考動画
ツイート内の動画では、更新ボタンをクリックすると、SQL Serverに接続して黄色のセルのデータを更新しています。(動画の0:13~0:38あたり)
先日SQL Serverをインストールしたので次にVBAからSQL Serverにアクセスして参照・更新処理ができるか確認してみました😄なんだかおかしなことやってますが💦
参照・追加・更新・削除の確認ができたのでこれでVBAとSQL Serverの連携が必要な処理を盛り込むことができます😁#Excel #vba#SQLServer pic.twitter.com/uqvLIu2fR3
— まさ@Excel、VBA、マクロ(経験年数16年) (@masamasa9785) February 24, 2021
最後に
本記事では、マクロからSQL Serverのデータを更新する方法についてご説明しました。
以下の処理を実装することで、SQL Serverのデータを更新することができます。
- ①SQL Serverへの接続情報取得
- ②Connectionオブジェクトのインスタンスの生成
- ③ ①の情報を参照してSQL Serverに接続
- ④Recordsetオブジェクトのインスタンスの生成
- ⑤Recordsetオブジェクトのインスタンスに、SQL Serverへの接続情報が設定されたConnectionオブジェクトと関連付ける
- ⑥データを更新するSQL文の設定
- ⑦データを更新
手順は多いですが、コードを実装する際は、先ほどお見せしたコードの例を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。