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

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

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

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

5. Command【ｺﾏﾝﾄﾞ】オブジェクトのCommandText【ｺﾏﾝﾄﾞﾃｷｽﾄ】プロパティ
に実行するSQLのUPDATE【ｱｯﾌﾟﾃﾞｲﾄ】文を設定します。

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

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

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【ﾅｯｼﾝｸﾞ】を代入してメモリー領域を解放します。

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

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

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