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円に変更する例

コード例

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

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 = _
“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文でレコードのデータを変更して更新する
方法についての解説を終了します。
ありがとうございました。

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

フォローする

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