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

スポンサーリンク

関連記事・広告