【ExcelVBA】マクロからSQL Serverにデータを追加するには

本記事では、マクロからSQL Serverにデータを追加する方法についてご説明します。

Table of Contents

マクロからSQL Serverにデータを追加する方法

マクロからSQL Serverにデータを追加するには、次の流れの通りにコードを書いていきます。

STEP.1

SQL Serverへの接続情報取得

SQL Serverに接続するための接続情報を取得します。

STEP.2

Connectionオブジェクトのインスタンスの生成

Connectionオブジェクトのインスタンスを生成します。
このインスタンスは、マクロがSQL Serverに接続するために必要です。

STEP.3

SQL Serverに接続

接続情報が取得出来たら、その接続情報をもとにSQL Serverに接続します。

STEP.4

Recordsetオブジェクトのインスタンスの生成

Recordsetオブジェクトのインスタンスを生成します。
このインスタンスは、SQL Serverにデータを追加するのに必要です。

STEP.5

Recordsetオブジェクトに接続先情報を設定

RecordsetオブジェクトのActiveConnectionプロパティに、STEP.1で取得した接続先情報が設定されているConnectionオブジェクトのインスタンスを設定します。
この設定により、Recordsetオブジェクトを開いたり操作することができるようになります。

STEP.6

データを追加するSQL文の設定

データを追加するSQL文をRecordsetオブジェクトのSourceプロパティに設定します。

STEP.7

データの追加

STEP.6のSQL文で指定したデータを追加します。
データの追加は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

実際のSQL(Insert)文

コード(例)の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行目)については次の記事で詳しく説明しているので、そちらもあわせてご覧ください。

4月 10, 2021【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