Excel VBA SQLのINSERT文でレコードを追加、DELETE文で削除する

SQLのINSERT文でレコードを追加、DELETE文で削除する方法

Excel VBA でSQL文を使用して、外部データベースのレコードのデータを追加または削除するには、ActiveX Data Objects【ｱｸﾃｨﾌﾞﾃﾞｰﾀｵﾌﾞｼﾞｪｸﾂ】（以下ADO）のライブラリーファイルへの参照設定を行い外部データベースの各オブジェクトのインスタンス（複製）を作成してSQLの実行を表す、Command【ｺﾏﾝﾄﾞ】オブジェクトのCommandText【ｺﾏﾝﾄﾞﾃｷｽﾄ】プロパティにSQLのレコードを追加するINSERET【ｲﾝｻｰﾄ】文レコードを削除するDELETE【ﾃﾞﾘｲﾄ】文を設定して実行します。

INSERT【ｲﾝｻｰﾄ】文でレコードを追加、DELETE【ﾃﾞﾘｲﾄ】文で削除する手順

1. Excelと外部データベースの接続を表す Connection【ｺﾈｸｼｮﾝ】オブジェクトのインスタンスを作成してオブジェクト変数に格納します。

2. データベースに対して問い合わせを行うSQL実行を表す Command【ｺﾏﾝﾄﾞ】オブジェクトのインスタンスを作成してオブジェクト変数に格納します。

3. Connection【ｺﾈｸｼｮﾝ】オブジェクトのOpen【ｵｰﾌﾟﾝ】メソッドを使用して外部データベースに接続します

4. Command【ｺﾏﾝﾄﾞ】オブジェクトのActiveConnection【ｱｸﾃｨﾌﾞｺﾈｸｼｮﾝ】プロパティ
に操作対象のConnection【ｺﾈｸｼｮﾝ】オブジェクト（外部データベース）を設定します。

5. Command【ｺﾏﾝﾄﾞ】オブジェクトのCommandText【ｺﾏﾝﾄﾞﾃｷｽﾄ】プロパティに実行するSQL文を設定します。SQL文の詳細についてはこちらをご覧ください。

6. 実行を表す、Command【ｺﾏﾝﾄﾞ】オブジェクトのExecute【ｴｸｽｷｭｰﾄ】メソッドを使用してINSERT【ｲﾝｻｰﾄ】文または、DELETE【ﾃﾞﾘｲﾄ】文を実行します。

INSERT文でAccessのテーブルのレコードにデータを追加する例

商品マスターテーブルにK商品のデータを追加するコードと解説

※ADOのライブラリファイルへの参照設定がされていることが前提のコード例です。

Sub INSERT文()
Dim コネクション As New ADODB.Connection
Dim コマンド As New ADODB.Command
コネクション.Open ConnectionString:= _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:¥Users¥Desktop¥Database1.accdb;"
With コマンド
.ActiveConnection = コネクション
.CommandText = _
"INSERT INTO 商品マスター(商品ｺｰﾄﾞ,商品名,仕入れ単価,販売単価) VALUES('0011000','K商品',700,1400);"
.Execute
End With
MsgBox "K商品のデータを追加しました。"
Set コマンド = Nothing
コネクション.Close: Set コネクション = Nothing
End Sub

2行目【Dim コネクション As New ADODB.Connection】
Dim【ﾃﾞﾑ】ステートメントでNew【ﾆｭｰ】キーワード使用して外部データベースとの接続を確立するConnection【ｺﾈｸｼｮﾝ】オブジェクトのインスタンスを生成してオブジェクト変数「コネクション」に格納します。

3行目【Dim コマンド As New ADODB.Command】
DimステートメントでNewキーワード使用してSQL文の実行を表すCommand【ｺﾏﾝﾄﾞ】オブジェクトのインスタンスを生成してオブジェクト変数「コマンド」に格納します。

4行目～6行目【コネクション.Open ConnectionString:= _
“Provider=Microsoft.ACE.OLEDB.12.0;” & _
“Data Source=C:¥Users¥Desktop¥Database1.accdb;”】
Connection【ｺﾈｸｼｮﾝ】オブジェクトのOpen【ｵｰﾌﾟﾝ】メソッドを使用して外部データベースの接続をします。

7行目【With コマンド】
Command【ｺﾏﾝﾄﾞ】オブジェクトのインスタンス（複製）が格納されている変数「コマンド」をWith【ｳｨｽﾞ】ステートメントで指定します。

8行目【.ActiveConnection = コネクション】
ActiveConnection【ｱｸﾃｨﾌﾞｺﾈｸｼｮﾝ】プロパティにSQLの対象となるConnection【ｺﾈｸｼｮﾝ】（接続）オブジェクトのインスタンスが格納されているオブジェクト変数「コネクション」
を設定します。

9行目～10行目【.CommandText = _
“INSERT INTO 商品マスター(商品ｺｰﾄﾞ,商品名,仕入れ単価,販売単価) VALUES(‘0011000′,’K商品’,700,1400);”】
CommandText【ｺﾏﾝﾄﾞﾃｷｽﾄ】プロパティに指定したテーブルにレコードを追加するSQLのINSERT【ｲﾝｻｰﾄ】文を設定します。INSERT　INTOに対象のテーブルを指定し()内にフィールド名を指定します。VALUES【ﾊﾞﾘｭｰｽﾞ】の()内に指定したフィールドに対応するデータを指定します。

11行目【.Execute】
指定したSQLを実行するExecute【ｴｸｽｷｭｰﾄ】メソッドを使用してINSERT【ｲﾝｻｰﾄ】文を実行します。

13行目【MsgBox “K商品のデータを追加しました。”】
MsgBox関数を使用してユーザーにデータの修正が終了したことを伝えます。

14行目【Set コマンド = Nothing】
Command【ｺﾏﾝﾄﾞ】オブジェクトのインスタンスが格納されているオブジェクト変数「コマンド」にNothing【ﾅｯｼﾝｸﾞ】を代入してメモリー領域を解放します。

15行目【コネクション.Close: Set コネクション = Nothing】
Connection【ｺﾈｸｼｮﾝ】オブジェクトのClose【ｸﾛｰｽﾞ】メソッドで外部データベースの接続を切断し、Nothing【ﾅｯｼﾝｸﾞ】を代入してメモリー領域を解放します。

実行結果

DELETE文でAccessのテーブルのレコードを削除する例

DELET文でACCESSのテーブルのK商品のレコードを削除するコードと解説

Sub DELETE文()
Dim コネクション As New ADODB.Connection
Dim コマンド As New ADODB.Command
コネクション.Open ConnectionString:= _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:¥Users¥Desktop¥Database1.accdb;"
With コマンド
.ActiveConnection = コネクション
.CommandText = _
"DELETE FROM 商品マスター WHERE 商品名 = 'K商品';"
.Execute
End With
MsgBox "K商品のデータを削除しました。"
Set コマンド = Nothing
コネクション.Close: Set コネクション = Nothing
End Sub

9行目～10行目【.CommandText = _
“DELETE FROM 商品マスター WHERE 商品名 = ‘K商品’;”】
Command【ｺﾏﾝﾄﾞ】オブジェクトのCommandText【ｺﾏﾝﾄﾞﾃｷｽﾄ】プロパティにDELETE【ﾃﾞﾘｲﾄ】文を設定してレコードを削除します。DELETE FROMに削除対象のレコードがあるテーブルを指定し、WHERE【ｳｪｱ】にレコードの検索文字列を指定します。なお、WHERE 【ｳｪｱ】を指定しない場合はテーブル内のすべてのレコードが対象になるので注意が必要です。

実行結果

セルの値を取得してレコードを追加するコード例

セルに入力されたデータでK商品のデータを追加するコードと解説

Sub INSERT2文()
Dim コネクション As New ADODB.Connection
Dim コマンド As New ADODB.Command
Dim 仕入 As Long
Dim 販売 As Long
Dim 商品 As String
Dim コード As String
仕入 = Range("C2").Value
販売 = Range("D2").Value
商品 = Range("B2").Value
コード = Range("A2").Value
コネクション.Open ConnectionString:= _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:¥Users¥Desktop¥Database1.accdb;"
With コマンド
.ActiveConnection = コネクション
.CommandText = _
"INSERT INTO 商品マスター(商品ｺｰﾄﾞ,商品名,仕入れ単価,販売単価) VALUES('" & コード & " ','" & 商品 & " ','" & CStr(仕入) & "','" & CStr(販売) & "');"
.Execute
End With
MsgBox 商品 & "を仕入れ単価" & 仕入 & "円" & 販売 & "円で追加しました。"
Set コマンド = Nothing
コネクション.Close: Set コネクション = Nothing
End Sub

4行目～7行目【Dim 仕入 As Long
Dim 販売 As Long
Dim 商品 As String
Dim コード As String】
仕入れ単価を格納する変数「仕入」と販売単価を格納する変数「販売」を長整数型（Long）で宣言します。商品名を格納する変数「商品」と商品ｺｰﾄﾞを格納する変数「コード」を文字列型（String）で宣言します。

8行目～11行目【仕入 = Range(“C2”).Value
販売 = Range(“D2”).Value
商品 = Range(“B2”).Value
コード = Range(“A2”).Value】
各変数に指定のセルの値を代入します。

17行目～18行目【.CommandText = _
“INSERT INTO 商品マスター(商品ｺｰﾄﾞ,商品名,仕入れ単価,販売単価) VALUES(‘” & コード & ” ‘,'” & 商品 & ” ‘,'” & CStr(仕入) & “‘,'” & CStr(販売) & “‘);”】
CommandText【ｺﾏﾝﾄﾞﾃｷｽﾄ】プロパティに設定するSQL文は文字列式で設定しなくてはなりません。VALUES【ﾊﾞﾘｭｰｽﾞ】に指定した変数「仕入」と変数「販売」のデータ型が数値なので
CStr【ｼｰｽﾄﾘﾝｸﾞ】関数を使用して文字列に変換します。

実行結果

InputBoxに削除する商品名を入力してそのレコードを削除する例

コード例

Sub DELETE文2()
Dim コネクション As New ADODB.Connection
Dim コマンド As New ADODB.Command
Dim 削除商品 As String
削除商品 = InputBox("データベースから削除する商品名を入力してください。")
コネクション.Open ConnectionString:= _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:¥Users¥Desktop¥Database1.accdb;"
With コマンド
.ActiveConnection = コネクション
.CommandText = _
"DELETE FROM 商品マスター WHERE 商品名 = '" & 削除商品 & "';"
.Execute
End With
MsgBox 削除商品 & "のデータを削除しました。"
Set コマンド = Nothing
コネクション.Close: Set コネクション = Nothing
End Sub

実行結果

以上で、SQLのINSERT文でレコードを追加、DELETE文で削除する方法についての解説を終了します。ありがとうございました。