Excel VBA ADOでトランザクション処理を実行する

スポンサーリンク

トランザクション処理とは

通常、外部データベースを更新する際には、1つの処理ごとにデータベースを更新しハードディスクにその変更を書き込みます。

例えば、「商品を販売した」という1つの取引処理で
1.「販売テーブル」に販売数量を書き込む
2.「在庫テーブル」から商品在庫を減らす
という2つの処理を行う場合、不具合が発生して、片方の処理が実行できない場合が考えられます。また、連続して大量の更新処理を実行するとハードディスクへの書き込みスピードが処理スピードに追いつかず、パフォーマンスの低下を招くことがあります。

そこで、複数の処理を実行したい場合は、処理の内容をメモリ内に記憶して最後に一括してデータの変更を反映させる方法にします。この方法であれば、ハードディスクに書き込む回数が1回で済むことと、複数の処理の中でエラーが発生した場合は更新作業自体を止めてしまえば
簡単にデータを元の状態に戻すことができるメリットがあります。

このような処理を「トランザクション処理」と呼び、効果的に利用することにより、データベースの速度向上と障害回避が期待できます。

ADOでトランザクション処理を実行する方法

ActiveX Data Objects【アクティブデータオブジェクツ】(以下ADO)でトランザクション処理を実行するには外部データベースとの接続を表すConnection【コネクション】オブジェクトの以下のメソッドを使用します。

メソッド 内容
BeginTrans【ビギントランス】 トランザクションの処理を開始します。
CommitTrans【コミットトランス】 BeginTrans記述後の変更処理を反映し
トランザクション処理を終了します。
RoolbackTrans【ロールバックトランス】 Begintrans記述後の変更処理をすべて破棄し
トランザクション処理を終了します。

各メソッドの書式と設定値の説明

オブジェクト変数.各メソッド


商品マスターテーブルに既に登録されてる
商品データを重複して登録した場合のエラー回避のトランザクション処理例


コードと解説

Sub INSERT3文()
Dim コネクション As New ADODB.Connection
Dim コマンド As New ADODB.Command
Dim 仕入 As Long
Dim 販売 As Long
Dim 商品 As String
Dim コード As String
仕入 = Range("C3").Value
販売 = Range("D3").Value
商品 = Range("B3").Value
コード = Range("A3").Value
コネクション.Open ConnectionString:= _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:¥Users¥Desktop¥Database1.accdb;"
With コマンド
.ActiveConnection = コネクション
.CommandText = _
"INSERT INTO 商品マスター(商品コード,商品名,仕入れ単価,販売単価) VALUES('" & コード & " ','" & 商品 & " ','" & CStr(仕入) & "','" & CStr(販売) & "');"
On Error GoTo エラー処理
コネクション.BeginTrans
.Execute
コネクション.CommitTrans
End With
MsgBox 商品 & "を仕入れ単価" & 仕入 & "円" & 販売 & "円で追加しました。"
Set コマンド = Nothing
コネクション.Close: Set コネクション = Nothing
Exit Sub
エラー処理:
MsgBox "商品は既にデータベースに登録されています。変更を破棄して終了します。"
コネクション.RollbackTrans
Set コマンド = Nothing
コネクション.Close: Set コネクション = Nothing
End Sub
19行目~22行目【On Error GoTo エラー処理
コネクション.BeginTrans
.Execute
コネクション.CommitTrans】

Command【コマンド】オブジェクトのExecute【エクスキュート】メソッドで実行を更新する前にConnection【コネクション】オブジェクトのBeginTrans【ビギントランス】メソッドでトランザクション処理を開始します。エラーが発生しなかった場合はConnection【コネクション】オブジェクトのCommitTrans【コミットトランス】メソッドで変更処理を更新してトランザクションを終了します。このケースでは既にデータベースにある商品コードと同じコードが登録されているのでExecute【エクスキュート】メソッドでエラーが発生するので9行目のOn Error【オンエラー】ステートメントとGoTo【ゴウトゥ】ステートメントを使用して
処理を「エラー処理:」ラベルがある18行目までジャンプします。


18行目~22行目【エラー処理:
MsgBox “商品は既にデータベースに登録されています。変更を破棄して終了します。”
コネクション.RollbackTrans
Set コマンド = Nothing
コネクション.Close: Set コネクション = Nothing】

エラーが発生した場合、18行目の「エラー処理:」ラベルまで処理がジャンプしてきます。MsgBox関数を使用してユーザーに処理を終了することを伝えます。Connection【コネクション】オブジェクトのRollbackTrans【ロールバックトランス】メソッドを使用して
エラーになった処理をすべて破棄してトランザクションの処理を終了します。

SQLの実行を表す、Command【コマンド】オブジェクトのインスタンスが格納されているオブジェクト変数「コマンド」にNothing【ナッシング】を代入してメモリーを解放し、接続を表す、Connection【コネクション】オブジェクトのインスタンスが
格納されているオブジェクト変数「コネクション」をClose【クローズ】メソッドで閉じて、Nothingを代入してメモリーを解放して実行前の状態に処理を戻します。

その他のコードの詳細解説はSQLのINSERT文でレコードを追加するをご覧ください。


実行結果


トランザクション処理を利用してExcelの1万行の商品データをAccessの
商品マスターテーブルに100行ごとに書き込む例



コードと解説

Sub INSERT4文()
Dim コネクション As New ADODB.Connection
Dim コマンド As New ADODB.Command
Dim コード As Long
Dim 商品 As String
Dim 仕入 As Long
Dim 販売 As Long
Dim i As Long
Dim 書込件数 As Long
i = 2
コネクション.Open ConnectionString:= _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:¥Users¥Desktop¥Database1.accdb;"
With コマンド
.ActiveConnection = コネクション
On Error GoTo エラー処理
コネクション.BeginTrans
Do Until Cells(i, 1).Value = ""
コード = Cells(i, 1).Value
商品 = Cells(i, 2).Value
仕入 = Cells(i, 3).Value
販売 = Cells(i, 4).Value
.CommandText = _
"INSERT INTO 商品マスター(商品コード,商品名,仕入れ単価,販売単価) VALUES('" & コード & " ','" & 商品 & " ','" & CStr(仕入) & "','" & CStr(販売) & "');"
.Execute
i = i + 1
If (i - 2) Mod 100 = 0 Then
コネクション.CommitTrans
コネクション.BeginTrans
End If
Loop
End With
コネクション.CommitTrans
MsgBox i - 2 & "件のレコードを書き込みました。"
Set コマンド = Nothing
コネクション.Close: Set コネクション = Nothing
Exit Sub
エラー処理:
コネクション.RollbackTrans
Application.Goto Cells(i, 1), True
書込件数 = Int((i - 2) / 100) * 100
MsgBox i & "行目のレコードでエラーが発生しました。" & 書込件数 & "件のレコードまで書き込んでいます。"
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行目【Dim コード As Long】
ワークシートの商品コードデータを格納する変数「データ」を長整数型(Long)で宣言します。


5行目【Dim 商品 As String】
ワークシートの商品名を格納する変数「商品」を文字列型(String)で宣言します。


6行目【Dim 仕入 As Long】
ワークシートの仕入れ単価を格納する変数「仕入」を長整数型(Long)で宣言します。


7行目【Dim 販売 As Long】
ワークシートの販売単価を格納する変数「販売」を長整数型(Long)で宣言します。


8行目【Dim i As Long】
繰り返し処理で使用するセルの行数を格納する変数「i」を長整数型(Long)で宣言します。


9行目【Dim 書込件数 As Long】
エラーが発生した場合、エラーが発生したセルの行数を格納する変数「書込件数」を長整数型(Long)で宣言します。


10行目【i = 2】
変数「i」に見出し行の1行目を除くために2を代入します。


11行目~13行目【コネクション.Open ConnectionString:= _
“Provider=Microsoft.ACE.OLEDB.12.0;” & _
“Data Source=C:¥Users¥Desktop¥Database1.accdb;”】

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


14行目【With コマンド】
Command【コマンド】オブジェクトのインスタンス(複製)が格納されている変数「コマンド」をWith【ウィズ】ステートメントで指定します。


15行目【.ActiveConnection = コネクション】
ActiveConnection【アクティブコネクション】プロパティにSQLの対象となるConnection【コネクション】(接続)オブジェクトのインスタンスが格納されているオブジェクト変数「コネクション」を設定します。


16行目【On Error GoTo エラー処理】
INSERT【インサート】文の更新処理でエラーが発生した場合、On Error【オンエラー】ステートメントとGoTo【ゴウトゥ】ステートメントを使用して処理を「エラー処理:」ラベルまで処理をジャンプします。


17行目【コネクション.BeginTrans】
Connection【コネクション】オブジェクトのBeginTrans【ビギントランス】メソッドでトランザクションの処理を開始します。


18行目【Do Until Cells(i, 1).Value = “”】
Do Loop【ドゥループ】ステートメントで繰り返し処理を定義します。Until【アンティル】(~ではない間)にA列のセルが空白ではない間つまり、「データが入力されている間繰り返す」を定義します。


19行目~22行目【コード = Cells(i, 1).Value
商品 = Cells(i, 2).Value
仕入 = Cells(i, 3).Value
販売 = Cells(i, 4).Value】

各変数に繰り返し処理の中各列のi行目の値を代入します。


23行目~24行目【.CommandText = _
“INSERT INTO 商品マスター(商品コード,商品名,仕入れ単価,販売単価) VALUES(‘” & コード & ” ‘,'” & 商品 & ” ‘,'” & CStr(仕入) & “‘,'” & CStr(販売) & “‘);”】

SQL文を設定するCommandオブジェクトのCommandText【コマンドテキスト】プロパティを使用して、レコードを追加するINSERT【インサート】文を設定します。CommandText【コマンドテキスト】プロパティに設定するSQL文は文字列式で設定しなくてはなりません。VALUESに指定した変数「仕入」と変数「販売」のデータ型が数値なのでCStr【シーストリング】関数を使用して文字列に変換します。


25行目【.Execute】
指定したSQLを実行するExecute【エクスキュート】メソッドを使用してINSERT【インサート】文を実行します


26行目【i = i + 1】
セルの行番号を表す、変数「i」に1を加算して次の行に対象セルを移動します。


27行目【If (i – 2) Mod 100 = 0 Then】
If【イフ】ステートメント(条件分岐)で割り算の余りをもとめる算術演算子のMod【モッド】で「(現在の行番号‐2)÷100 = 余り0だったとき」を定義します。(現在の行番号‐2)の‐2は26行目で行番号を1加算していることと見出し行分の1を減算しています。条件式が成立した場合、つまりデータの行番号が100単位のなった場合、次のCommitTrans【コミットトランス】メソッドで
データの書き込みを確定してトランザクションを終了します。これで、データを100行単位で書き込んでいることになります。


28行目~31行目【コネクション.CommitTrans
コネクション.BeginTrans
End If
Loop】

27行目の条件分岐が成立した場合、つまりセルの行番号が100単位の場合、Connection【コネクション】オブジェクトのCommitTrans【コミットトランス】メソッドでINSERT文を確定してトランザクションを終了しConnection【コネクション】オブジェクトの
BeginTrans【ビギントランス】メソッドで再びトランザクションを開始します。Loopで18行目の繰り返し処理の始まりに戻ります。27行目の条件式が成立しない場合つまり現在の行番号が100単位でない場合はこの処理は行われず18行目に処理が戻ります。


33行目【コネクション.CommitTrans】
ワークシートのA列のセルが空白になったとき繰り返し処理を抜けてまだ更新されていない書き込んだデータをCommitTrans【コミットトランス】メソッドで更新してトランザクション処理を終了します。


34行目【MsgBox i – 2 & “件のレコードを書き込みました。”】
MsgBox関数を使用してユーザーに書き込む件数を伝えます。


35行目~37行目【Set コマンド = Nothing
コネクション.Close: Set コネクション = Nothing
Exit Sub】

Command【コマンド】オブジェクトのインスタンス(複製)が格納されているオブジェクト変数「コマンド」にNoting【ナッシング】を代入して、メモリーを解放し。Connection【コネクション】オブジェクトのインスタンスが格納されているオブジェクト変数「コネクション」をClose【クローズ】メソッドを使用して接続を閉じてNoting【ナッシング】を代入してメモリーを解放します。Exit【エグジット】ステートメントでSub【サブ】プロシージャーを途中で終了します。


38行目~39行目【エラー処理:
コネクション.RollbackTrans】

これ以降はエラーが発生した場合の処理です。Connection【コネクション】オブジェクトのRollbackTrans【ロールバックトランス】メソッドを使用してINSERT【インサート】処理を破棄してトランザクションを終了します。


40行目【Application.Goto Cells(i, 1), True】
Application【アプリケーション】オブジェクトのGoTo【ゴウトゥ】メソッドを使用してエラーが発生したセルにスクロールします。


41行目【書込件数 = Int((i – 2) / 100) * 100】
変数「書込件数」にエラーが発生する前までに100行単位で書き込みが確定している行数を代入します。小数点以下を切り捨てるInt【イント】関数を使用してエラーが発生した行番号を100で割って100単位以下を小数点にし、切り捨てた後100を掛けることのよりエラーが発生する前に書き込みが終了している行数をもとめて変数「書込件数」に代入します。


42行目【MsgBox i & “行目のレコードでエラーが発生しました。” & 書込件数 & “件のレコードまで書き込んでいます。”】
MsgBox関数を使用してユーザーにエラーが発生したことと書き込みが終了している件数を伝えます。


43行目~45行目【Set コマンド = Nothing
コネクション.Close: Set コネクション = Nothing
End Sub】

Command【コマンド】オブジェクトのインスタンス(複製)が格納されているオブジェクト変数「コマンド」にNoting【ナッシング】を代入して、メモリーを解放し。Connection【コネクション】オブジェクトのインスタンスが格納されているオブジェクト変数「コネクション」をClose【クローズ】メソッドを使用して接続を閉じて、Noting【ナッシング】を代入してメモリーを解放します。
End【エンド】ステートメントでSubプロシージャーを終了します。


実行結果



エラーが発生した場合




以上で、ADOでトランザクション処理を実行するについての解説を終了します。ありがとうございました。

スポンサーリンク

関連記事・広告