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でトランザクション処理を実行するについての解説を終了します。
ありがとうございました。

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

フォローする

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