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商品のデータを追加するコード例

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

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

コードの解説

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文で削除する
方法についての解説を終了します。
ありがとうございました。

スポンサーリンク
スポンサーリンク

フォローする

スポンサーリンク
スポンサーリンク