本記事では、マクロからSQL Serverにデータを追加する方法についてご説明します。
マクロからSQL Serverにデータを追加する方法
マクロからSQL Serverにデータを追加するには、次の流れの通りにコードを書いていきます。
このインスタンスは、マクロがSQL Serverに接続するために必要です。
このインスタンスは、SQL Serverにデータを追加するのに必要です。
この設定により、Recordsetオブジェクトを開いたり操作することができるようになります。
データの追加はRecordsetオブジェクトのOpenメソッドで行います。
コードの例
Excelのマクロのコード(例)
Excelのマクロのコード(例)は次の通りです。
' Dim DBName As String, connDB As String, sqlStr 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文(Insert文)をSourceプロパティに設定する sqlStr = "INSERT INTO dbo.tbl_userInfo (" sqlStr = sqlStr & "memID" sqlStr = sqlStr & ", memName" sqlStr = sqlStr & ", postalCode" sqlStr = sqlStr & ", addrs" sqlStr = sqlStr & ", phoneNum" sqlStr = sqlStr & ", birth" sqlStr = sqlStr & ", mailaddrs" sqlStr = sqlStr & ", delFlg) " sqlStr = sqlStr & "VALUES " sqlStr = sqlStr & "(" sqlStr = sqlStr & 10 sqlStr = sqlStr & ",N'" & "ユーザ10" & "'" sqlStr = sqlStr & ",'" & "123-4567" & "'" sqlStr = sqlStr & ",N'" & "住所1-1-1" & "'" sqlStr = sqlStr & ",'" & "080-1234-5678" & "'" sqlStr = sqlStr & ",'" & 19870511 & "'" sqlStr = sqlStr & ",'" & "abc@xyz.com" & "'" sqlStr = sqlStr & ",0)" 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
コード(例)の30行目から48行目で記述したSQL(Insert)文は次の通りです。
INSERT INTO dbo.tbl_userInfo (
memID
,memName
,postalCode
,addrs
,phoneNum
,birth
,mailaddrs
,delFlg
)
VALUES
(
10
,N’ユーザ10′
,’123-4567′
,N’住所1-1-1′
,’080-xxxx-1234′
,’19870511′
,’abc@xyz.com’
,0
)
コード(例)だとInsert文が分かりにくいと思ったので参考にしていただければと思います。
コードの解説
最初に見て頂きたいのは52行目です。
RecordsetオブジェクトのOpenメソッドを使い、テーブルにデータを追加しています。
なお、追加するデータはSQL文で記述し(30行目から48行目)、Sourceプロパティに指定します。(49行目)
また、SQL Serverにデータを追加するにはまずSQL Serverに接続しなければいけませんが、マクロからSQL Serverに接続する処理(10行目から19行目)については次の記事で詳しく説明しているので、そちらもあわせてご覧ください。
【ExcelVBA】マクロからSQL Serverに接続するには文字化けに注意
Excelのマクロのコード(例)の42行目と44行目で「N」の文字が使われているのが分かりますでしょうか?
この「N」がないと、データの追加処理が行われると全角文字が「?」に文字化けします。
この「N」の文字は、データを追加する際に全角文字が文字化けないようにするのに必要な文字です。(半角文字や数値は文字化けしないので必要はありません)
なので、全角文字のデータを追加する場合は「N」の文字を忘れないようにしましょう。
【参考:文字化けしたデータの例(画像)】
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、先ほどのコードの3行目の「ADODB.Connection」と4行目の「ADODB.Recordset」というオブジェクトが、「msado28.tlb」というファイルを参照するからです。
Dim oCon As ADODB.Connection Dim oRS As ADODB.Recordset
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」と「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
【動画】マクロからSQL Serverのデータを追加する参考動画
ツイート内の動画では、Excelのシートの下部にあるコンボボックスに「追加」を選択して黄色のセルに追加したいデータを入力後に実行ボタンをクリックすると、SQL Serverに接続して黄色のセルのデータを追加しています。(動画の1:06~1:40あたり)
先日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週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。