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