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

• オブジェクト変数
  接続を表す、Connection【ｺﾈｸｼｮﾝ】オブジェクトのインスタンスが
  格納されているオブジェクト変数を指定します。
• 各メソッド
  上記のトランザクション処理を実行するメソッドを指定します。

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

18行目～22行目【エラー処理:
MsgBox “商品は既にデータベースに登録されています。変更を破棄して終了します。”
コネクション.RollbackTrans
Set コマンド = Nothing
コネクション.Close: Set コネクション = Nothing】
エラーが発生した場合、18行目の「エラー処理：」ラベルまで処理がジャンプしてきます。MsgBox関数を使用してユーザーに処理を終了することを伝えます。Connection【ｺﾈｸｼｮﾝ】オブジェクトのRollbackTrans【ﾛｰﾙﾊﾞｯｸﾄﾗﾝｽ】メソッドを使用して
エラーになった処理をすべて破棄してトランザクションの処理を終了します。

SQLの実行を表す、Command【ｺﾏﾝﾄﾞ】オブジェクトのインスタンスが格納されているオブジェクト変数「コマンド」にNothing【ﾅｯｼﾝｸﾞ】を代入してメモリーを解放し、接続を表す、Connection【ｺﾈｸｼｮﾝ】オブジェクトのインスタンスが
格納されているオブジェクト変数「コネクション」をClose【ｸﾛｰｽﾞ】メソッドで閉じて、Nothingを代入してメモリーを解放して実行前の状態に処理を戻します。

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

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プロシージャーを終了します。