Excel VBA SQLのUPDATE文でレコードのデータを変更して更新する

スポンサーリンク

SQLのUPDATE文でレコードのデータを変更して更新する方法

Excel VBA でSQL文を使用して、外部データベースのレコードのデータを変更して更新するにはActiveX Data Objects【アクティブデータオブジェクツ】(以下ADO)のライブラリーファイルへの参照設定を行い外部データベースの各オブジェクトのインスタンス(複製)を作成して各メソッドやプロパティを使用して外部データベースのテーブルのレコードを変更して更新します。

UPDATE文でレコードのデータを変更して更新する手順

1. Excelと外部データベースの接続を表すConnection【コネクション】オブジェクトのインスタンスを作成してオブジェクト変数に格納します。


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


3. Connection【コネクション】オブジェクトのOpen【オープン】メソッドを使用して外部データベースに接続します


4. Command【コマンド】オブジェクトのActiveConnection【アクティブコネクション】プロパティ
に操作対象のConnection【コネクション】オブジェクト(外部データベース)を設定します。


5. Command【コマンド】オブジェクトのCommandText【コマンドテキスト】プロパティ
に実行するSQLのUPDATE【アップデイト】文を設定します。


6. 実行を表す、Command【コマンド】オブジェクトの Execute【エクスキュート】メソッドを使用してUPDATE文を実行します。


SQLのUPDATE文でAccessの「商品マスター」テーブルの「A商品」の
「仕入れ単価」を700円、「販売単価」を1400円に変更する例

コードと解説

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

Sub UPDATE文()
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 = _
"UPDATE 商品マスター SET 仕入れ単価=700, 販売単価=1400 WHERE 商品名='A商品';"
.Execute
End With
MsgBox "A商品のデータを修正しました。"
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 = _
“UPDATE 商品マスター SET 仕入れ単価=700, 販売単価=1400 WHERE 商品名=’A商品’;”】

CommandText【コマンドテキスト】プロパティに検索したレコードの指定したフィールドのデータを変更して更新するSQLのUPDATE【アップデイト】文を設定します。UPDATE【アップデイト】に対象のテーブルを指定しSET【セット】に値を変更するフィールドと変更値を設定しWHERE【ウェア】に操作対象のレコードを絞り込む条件式を設定します。WHERE【ウェア】を省略した場合は、すべてのレコードが操作対象になります。


11行目【.Execute】
指定したSQLを実行するExecute【エクスキュート】メソッドを使用してSELECT【セレクト】文を実行します。


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


14行目【Set コマンド = Nothing】
Command【コマンド】オブジェクトのインスタンスが格納されているオブジェクト変数「コマンド」にNothing【ナッシング】を代入してメモリー領域を解放します。


15行目【コネクション.Close: Set コネクション = Nothing】
Connection【コネクション】オブジェクトのClose【クローズ】メソッドで外部データベースの接続を切断し、Nothing【ナッシング】を代入してメモリー領域を解放します。


実行結果


セルに入力されたデータを使用してAccessのレコードのデータを変更する例

実行前


コードと解説

Sub UPDATE文2()
Dim コネクション As New ADODB.Connection
Dim コマンド As New ADODB.Command
Dim 仕入 As Long
Dim 販売 As Long
Dim 商品 As String
仕入 = Range("B2").Value
販売 = Range("C2").Value
商品 = Range("A2").Value
コネクション.Open ConnectionString:= _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:¥Users¥Desktop¥Database1.accdb;"
With コマンド
.ActiveConnection = コネクション
.CommandText = _
"UPDATE 商品マスター SET 仕入れ単価= '" & CStr(仕入) & "', 販売単価= '" & CStr(販売) & "' WHERE 商品名='" & 商品 & "';"
.Execute
End With
MsgBox 商品 & "の仕入れ単価を" & 仕入 & "円 販売単価を" & 販売 & "円に修正しました。"
Set コマンド = Nothing
コネクション.Close: Set コネクション = Nothing
End Sub
4行目~6行目【Dim 仕入 As Long
Dim 販売 As Long
Dim 商品 As String】

変更する仕入れ単価を格納する変数「仕入」と、変更する販売単価を格納する変数「販売」を
長整数型(Long)で宣言します。変更するレコードを絞り込む条件式で使用する値を格納する変数「商品」を文字列型(String)で宣言します。


7行目~9行目【仕入 = Range(“B2”).Value
販売 = Range(“C2”).Value
商品 = Range(“A2”).Value】
各変数に指定のセルの値を代入します。


15行目~16行目【.CommandText = _
“UPDATE 商品マスター SET 仕入れ単価= ‘” & CStr(仕入) & “‘, 販売単価= ‘” & CStr(販売) & “‘ WHERE 商品名='” & 商品 & “‘;”】

CommandText【コマンドテキスト】プロパティに設定するSQL文は文字列式になるので、SETに指定した変数「仕入」と変数「販売」のデータ型が数値なのでCStr【シーストリング】関数を使用して文字列に変換します。


実行結果


以上で、SQLのUPDATE文でレコードのデータを変更して更新する方法についての解説を終了します。ありがとうございました。

スポンサーリンク

関連記事・広告