【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
    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文(Delete文)をSourceプロパティに設定する
    oRS.Source = "Delete from dbo.tbl_userInfo where memID = '1'" 

    'データを削除する
    oRS.Open

    '各終了処理
    oRS.Close
    oCon.Close
    If Not oRS Is Nothing Then Set oRS = Nothing
    If Not oCon Is Nothing Then Set oCon = Nothing

コードの解説

最初に見て頂きたいのは32行目です。

RecordsetオブジェクトのOpenメソッドを使い、データを削除しています。

なお、削除するデータはSQL文で記述し、Sourceプロパティに指定します。(30行目)

また、SQL Serverのデータを削除するにはまずSQL Serverに接続しなければいけませんが、マクロからSQL Serverに接続する処理(10行目から19行目)については次の記事で詳しく説明しているので、そちらもあわせてご覧ください。

4月 10, 2021【ExcelVBA】マクロからSQL Serverに接続するには

目次へ戻る

【注意】参照設定が必要です

一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。

参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「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のデータを削除する参考動画

ツイート内の動画では、Excelのシートの下部にあるコンボボックスに「削除」を選択して実行ボタンをクリックすると、SQL Serverに接続してデータを追加しています。(動画の0:43～0:49あたり)

先日SQL Serverをインストールしたので次にVBAからSQL Serverにアクセスして参照・更新処理ができるか確認してみました😄なんだかおかしなことやってますが💦

参照・追加・更新・削除の確認ができたのでこれでVBAとSQL Serverの連携が必要な処理を盛り込むことができます😁#Excel #vba #SQLServer pic.twitter.com/uqvLIu2fR3

— まさ@Excel、VBA、マクロ（経験年数16年） (@masamasa9785) February 24, 2021