ExcelVBAとAccessの連携 第6回 トランザクション処理

ExcelVBAとAccessの連携 第6回 トランザクション処理

ながいことやっております、ExcelからAccessのデータベースを利用しちゃおうシリーズ。遂に今回で最終回です。実際の処理は前回までで出来るのですが、実用性を高めようという内容です。


関連記事

この連載がもっと実用的なサンプルで書籍になりました！

トランザクションとは

まずはこちらの引用を。

  • 関連する複数の処理を一つの処理単位としてまとめたもの
  • トランザクションとして管理された処理は「すべて成功」か「すべて失敗」のいずれかであることが保証される
  • どちらか一方が失敗したらもう片方も失敗させ、どちらも成功したときに初めて全体を成功と評価する
トランザクションとは ： IT用語辞典より抜粋

DBを使ったシステムの場合、複数の処理を伴うことが多く、何らかのエラーで途中で処理が止まってしまうと不具合が出る可能性が多々あります。

130918-1

この図のように、ハンパな処理がDB上に存在してしまうことになります。そこでトランザクション処理を実装すると、

130918-2

全てOKのときに初めて確定、途中でエラーが起きたらそれまでの処理をなかったことにしてくれる、ということが出来ます。

例

Sub sample()
  Call DB接続

  On Error GoTo Err_Handler 'エラーが起きたら"Err_Handler"へ
  adoCn.BeginTrans 'トランザクション開始

  '処理1
  '処理2
  '処理3
  '処理4
  '・・・

  adoCn.CommitTrans 'トランザクション終了（確定処理）
  Call DB切断

  MsgBox "正常に更新完了しました"
  Exit Sub '正常ならここで終了

Err_Handler: 'エラーが起きたらここへ飛ぶ
  adoCn.RollbackTrans 'ロールバック
  Call DB切断
  MsgBox Error$ 'エラーメッセージ表示
End Sub

ざっくり書くとこんな感じでしょうか。正常終了なのか異常終了なのか、なんのエラーが出たのか、というのを知るためにメッセージボックスをつけると分かりやすいです。

まとめ

さて、第6回にまで渡ったこのシリーズですが、途中でごねごねしてて分かりにくいところもあったと思いますので（すみません）、トランザクションまで含めてテンプレートを書きなおしてみました。

Private adoCn As Object 'ADOコネクションオブジェクト
Private adoRs As Object 'ADOレコードセットオブジェクト
Private strSQL As String 'SQL文
'Private Const DBpath As String = "C:\tweet.mdb" '接続するファイル（～2003）のフルパス
Private Const DBpath As String = "C:\tweet.accdb" '接続するファイル（2007～）のフルパス

Sub DBconnect(flg As Boolean) 'DB接続プロシージャ
  Set adoCn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成
  If flg = True Then Set adoRs = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成
  'adoCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBpath & ";" 'Accessファイル（～2003）を開く
  adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBpath & ";" 'Accessファイル（2007～）を開く
End Sub

Sub DBcut_off(flg As Boolean) 'DB切断プロシージャ
  If flg = True Then
    adoRs.Close 'レコードセットのクローズ
    Set adoRs = Nothing
  End If
  adoCn.Close 'コネクションのクローズ
  Set adoCn = Nothing
End Sub

Sub DBprocess() '処理
  Dim 処理内で使う変数宣言

'読込の場合------------------------------
  Call DBconnect(True) 'DB接続

  strSQL = "" 'SQL文(SELECT)
  adoRs.Open strSQL, adoCn 'SQLを実行して対象をRecordSetへ
  Range("A1").CopyFromRecordset adoRs 'セルへ出力（第3回記事参照）

  Call DBcut_off(True) 'DB切断
'------------------------------読込の場合ここまで

'書込・更新・削除の場合-------------------
  If 条件 Then 'DB操作までの条件に満たない場合
    MsgBox "メッセージ"
    Exit Sub '終了
  End If

  Call DBconnect(False) 'DB接続

  On Error GoTo Err_Handler 'エラーが起きたら"Err_Handler"へ
  adoCn.BeginTrans 'トランザクション開始

    strSQL = "" 'SQL文（INSERT、UPDATE、DELETE）
    adoCn.Execute strSQL '実行

  adoCn.CommitTrans 'トランザクション終了（確定処理）
  Call DBcut_off(False) 'DB切断

  MsgBox "正常に完了しました"
  Exit Sub '正常ならここで終了

Err_Handler: 'エラーが起きたらここへ飛ぶ
  adoCn.RollbackTrans 'ロールバック
  Call DBcut_off(False) 'DB切断（adoRs.Openをする場合はTrue）
  MsgBox Error$ 'エラーメッセージ表示
'-------------------書込・更新・削除の場合ここまで
End Sub

ずばっとコピペすればだいたい使えるんじゃないでしょうか。細かいSQL文などの処理は各回をご参照ください。

モジュールをまたいだりユーザーフォームを使ったりする場合は、冒頭の宣言はPrivateじゃなくてPublicだと幅広く使えます。

詳しくはこちら

あとがき

やっっと最後まで書けました。

当時、仕事でExcelとAccess連携させたら楽になりそうだよな～と妄想してから実際調べて書いてみて、運用に至るまでに結構な時間がかかりました。運用後も、実はここの部分よく分かってないんだよね…とか、ちょっと非効率的な動きしてるけどまぁいっか、みたいなところもあって、自分の勉強のためにもまとめてみよう！と思い至った次第です。

このブログのExcelVBAカテゴリは、私が駆け出しの頃「こういう解説してくれてるサイトがあったらなぁ」と思っていたことを自分なりにやってみて、同じような立場の方の時間短縮になれたら嬉しいな、という気持ちで書いています。実現出来ているかは分かりませんが、どなたかのお役に立てていたら、とても光栄に思います。

また、私もまだまだ勉強中の身ですので、間違ってるところやお気づきの点がありましたらご指摘くださると有難いです。ここまで目を通して頂きまして、ありがとうございました！

改善コード追記（2017/4/10）

この記事を書いてから何年も経ってしまっているのですが、今更になって「接続と切断のプロシージャを呼び出すのに、レコードセットオブジェクトを使うかどうか判断して True/False 指定するのって面倒じゃない…？」「もっとうまいことできないの…？」と、思ってしまいました。

そもそもレコードセット作成の記述を共通プロシージャに書いてるのがイケてない気がする…。

というわけで、レコードセットオブジェクト作成は接続部ではなく.Openの直前に書いて、切断部ではレコードセットオブジェクトの有無によってクローズ、のように修正しました。これなら True/False を指定しなくていいです。

Private adoCn As Object 'ADOコネクションオブジェクト
Private adoRs As Object 'ADOレコードセットオブジェクト
Private strSQL As String 'SQL文
Private Const DBpath As String = "C:\tweet.accdb" '接続するファイル（2007～）のフルパス

Sub DBconnect() 'DB接続プロシージャ
  Set adoCn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成
  adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBpath & ";" 'Accessファイル（2007～）を開く
End Sub

Sub DBcut_off() 'DB切断プロシージャ
  If Not adoRs Is Nothing Then 'レコードセットオブジェクトが作成されていたら
    adoRs.Close 'レコードセットのクローズ
    Set adoRs = Nothing
  End If
  adoCn.Close 'コネクションのクローズ
  Set adoCn = Nothing
End Sub

Sub DBprocess() '処理
  Dim 処理内で使う変数宣言

'読込の場合------------------------------
  Call DBconnect 'DB接続

  strSQL = "" 'SQL文(SELECT)
  Set adoRs = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成
  adoRs.Open strSQL, adoCn 'SQLを実行して対象をRecordSetへ
  Range("A1").CopyFromRecordset adoRs 'セルへ出力（第3回記事参照）

  Call DBcut_off 'DB切断
'------------------------------読込の場合ここまで

'書込・更新・削除の場合-------------------
  If 条件 Then 'DB操作までの条件に満たない場合
    MsgBox "メッセージ"
    Exit Sub '終了
  End If

  Call DBconnect 'DB接続

  On Error GoTo Err_Handler 'エラーが起きたら"Err_Handler"へ
  adoCn.BeginTrans 'トランザクション開始

    strSQL = "" 'SQL文（INSERT、UPDATE、DELETE）
    adoCn.Execute strSQL '実行

  adoCn.CommitTrans 'トランザクション終了（確定処理）
  Call DBcut_off 'DB切断

  MsgBox "正常に完了しました"
  Exit Sub '正常ならここで終了

Err_Handler: 'エラーが起きたらここへ飛ぶ
  adoCn.RollbackTrans 'ロールバック
  Call DBcut_off 'DB切断
  MsgBox Error$ 'エラーメッセージ表示
'-------------------書込・更新・削除の場合ここまで
End Sub

これまでのコードでももちろん動くのですが、こっちのほうがスマートなんじゃないかなと思ったので、追記しておきます。

更に追記（2017/5/1）

書籍化なども経て、さらにスマートに書き直したコードもまとめました。

公開日：2013/09/18
更新日：2017/05/01

ExcelVBAに興味をお持ちの方は、こちらの記事もどうぞ！

書籍を執筆しています。

はてブ Pocket feedly

12件のコメント

  1. hiro より:
    2013年10月30日 12:13 AM

    はじめまして。
    楽しく拝見させていただきました！

    なかなかexcelとaccessのDB連携についてわかりやすくかかれたサイトがなかったので非常に助かりました。
    デザインもおしゃれで見やすかったです。

    今後はフォームやコントロールを使ったVBAプログラムの記事があったら・・・ということを期待しています笑

    返信
    • *you より:
      2013年10月30日 2:07 PM

      hiroさん、はじめまして！コメントありがとうございます。
      有難いお言葉、本当に本当に感無量です。まとめてみて良かったです…｡･ﾟ･(ﾉД`)･ﾟ･｡

      リクエストもありがとうございますー！書いてみたいネタはいくつかあるのですが、現在、仕事で使っているOfficeが未だ2003のため、個人でもまだ（メンテとかのために）2003から抜け出せずにいるんです。今時2003を使ってFormの説明しても2007以降で見た目も機能も違うんじゃないのかな、という気持ちからなかなか書けずにいまして…。

      でも、来春にはOfficeを2013へ一斉バージョンアップする予定で、それに慣れたらそっち系の記事も書けたらなと思っています！2003→2013への移行不具合は今からハラハラしておりますが…(;´Д`)

  2. ARIES10 より:
    2013年11月23日 12:17 PM

    こんにちは。

    大変ためになりました。
    頭の中でわかっていることを、よくわかっていない人の視点にたって噛み砕いて説明することは難しいし、手間のかかることだとおもいます。

    でもそれを実際にしてくださっていることで、私のような者はとてもとっつきやすくエクセルVBA＆アクセスに取り組めるようになります。

    今エクセルVBAとアクセスを使ってやりたいことがあり
    質問サイトで質問したところご紹介頂いてここに来ました。
    大変感謝しています。

    ぜひ一言お礼を言いたくて書き込ませていただきました。

    今後も頑張ってください！

    返信
    • *you より:
      2013年11月25日 11:13 AM

      ARIES10さん、コメントありがとうございます！

      そのようなお言葉をいただけて、本当に光栄の極みです…！この連携については私もかなり試行錯誤した結果ですので、こんな私の方法がお役に立ててとっても嬉しいです。お言葉に甘えて、今後も頑張らせて頂きます！ありがとうございました！

  3. ARIES10 より:
    2013年12月4日 11:57 PM

    こんばんは。

    本日夕方よりご例示いただいているツイートシステムを
    作ってみました。

    記事を読むだけでなく、自分のエクセルでの実装も
    無事トランザクション処理まですべて行えました！

    本当にわかりやすかったです。
    改めてありがとうございました。

    もしよかったら、なのですが、
    私はご例示いただいたVBAを応用して、在庫管理テーブルの読み込み、編集、更新をしようとしているのですが、
    今回のVBAと同じように読み込んだ後、違う点として
    編集するレコードが複数の場合があります。
    このときに、読み込んだレコード達のうち、
    追加で「更新対象」という列のチェックボックスにチェックが入っているものはすべて更新していく、というような
    ループ処理も可能なのでしょうか。

    もし簡単にご教示いただけるようでしたらご教示いただけると大変うれしいです。もし簡単でなかったりお忙しいようでしたら、大丈夫です。

    返信
    • *you より:
      2013年12月5日 10:56 AM

      ARIES10さん、こんにちは。
      実装報告して頂けたのは初めてなので嬉しいですー！ちゃんと動いて良かった！

      わたしが書いた記事では、編集（UPDATE文）の後、SELECT文にて再読み込みするということを「更新」と表現したつもりでしたが、その意味でよろしいでしょうか？

      管理者注：この記事を書いた当時名称を混同しておりまして、SELECTでのデータ読込を画面更新のつもりで「更新」、UPDATEのことを「編集」と書いてしまったため質問者さんにご迷惑をおかけしました。現在は記事のほうは修正してあります。

      ループを使って一部だけを再読み込みすることは可能とは思いますがおそらく結構面倒です。複数箇所をちょっとずつ再読み込みするよりは、シート上のデータを全て再描写してしまったほうが簡単で結果も同じなのではないのかな、と思います。

      説明では、分かりやすくするために編集（UPDATE文）した後、「更新」ボタンを押さないとシートに反映されないようになっていますが、

      Sub 編集プロシージャ()
  Call DB接続
  strSQL = "UPDATE (略)"
  adoCn.Execute strSQL '実行
  Call DB切断
  
  Call 更新プロシージャ
End Sub

      のように、編集プロシージャの最後で更新プロシージャ（更新ボタンを押すのと同じ動きをするところ）を呼び出すようにしておけば、編集直後にシートが自動で更新されるのでストレスなく操作できるようになると思いますが、いかがでしょうか。ご期待に添えれば良いのですが…。

  4. ARIES10 より:
    2013年12月5日 11:48 AM

    おはようございます。

    早速お返事頂けて感激しております。
    ご親切にありがとうございます！

    >わたしが書いた記事では、編集（UPDATE文）の後、SELECT文にて再読み込みするということを「更新」と表現したつもりでしたが、その意味でよろしいでしょうか？

    申し訳ございません。
    その「更新」のことではありません。
    説明が下手で失礼いたしました。

    記事内で、「編集」で行っている処理を複数レコード分一気にやりたいというものでした。

    No.　アイテム　サイズ　在庫　対象
    １　　服A　　　S　　　３
    ２　　服A　　　M　　　４
    ３　　服A　　　L　　　５
    ４　　服B　　　L　　　２

    あるアイテムの服AのSとMが一着ずつ売れた時、
    服Aと打ち込んで「更新」ボタンをおせば
    No.1と2と3が表示されると思います。
    このとき、No.1と2を更新したいときに
    編集ボタンを押して、一つずつ更新したい値を
    入れるのではなく、明細上で数値を書き換え、
    たとえば対象という右端の列に「１」と記入した
    レコードのもののみをLOOPで見つけて「編集」処理を
    やっていくということです。

    ちょっと仕様の違いがご例示のVBAとは異なってきてしまうでしょうか？

    すみません、VBAを始めたばかりでそのあたりの感覚がまだなくて。m(_ _)m

    よかったら、ご返信ください。

    返信
    • *you より:
      2013年12月5日 12:50 PM

      ああ、やはりUPDATEのことでしたね…！こちらこそすみません。
      上記のこと、問題なく実装できます。むしろ、このようなたくさんの処理をすることこそExcel&Accessの醍醐味（？）ですよね。
      ちょっとコメント欄では説明するスペースが小さすぎますので、記事に追記させて頂きますね。

  5. ARIES10 より:
    2013年12月5日 1:04 PM

    続けてすみません。

    ご例示のVBAについて、一点質問させてください。

    >i = 10 ‘スタート行
    Do Until adoRs.EOF ‘レコードセットが終了するまで処理を繰り返す
    Cells(i, 2) = adoRs!f_no
    Cells(i, 3) = adoRs!f_id
    Cells(i, 4) = adoRs!f_name
    Cells(i, 5) = adoRs!f_content
    Cells(i, 6) = adoRs!f_day
    i = i + 1 ‘行をカウントアップする
    adoRs.MoveNext ‘次のレコードに移動する
    Loop
    この場合は、冒頭で変数iを整数型でDim宣言してください。

    こちらに切り替えてみました。
    宣言は
    Dim acnt As String, dy As String, txt As String, i As Integer
    とiを整数型で宣言してみました。

    しかしエラーになりました。
    デバッグを押すと
    Cells(i, 2) = adoRs!f_no
    で止まっていました。
    アプリケーション定義またはオブジェクト定義のエラーです、とのことです。

    試しに、宣言のすぐ下にて
    i = 1234
    msgbox i
    exit sub
    と打ったら、画面にちゃんと1234と出たので
    変数の宣言は正しいのかと思います。

    cellsの中にiを入れられないのでしょうか？
    ググってみるとRangeという関数とともにcellsは
    登場してきますが、cellsが整数型を扱えないと
    いうような記述はなく、エラーの原因が特定できずにいます。

    cellsの方が、好きな取得項目を好きな列に
    配置できそうで、そちらを使ってみたいので、
    ご教示いただけますでしょうか。

    お手数おかけいたします。m(_ _)m

    返信
    • *you より:
      2013年12月6日 12:40 AM

      ええと、まず、複数のUPDATEについては第5回の記事に追記しましたので、ご参照ください。

      エラーについては、申し訳ありませんが、こちらでは再現することができませんでした。そこで止まるのならばSQLの実行は通っているはずですし、.CopyFromRecordsetでデータが取り出せるならこちらの形式でも問題ないと思うのですが…。Cellsの中にiを使えることは間違いないので、レコードからフィールドを取り出すのがうまくいっていないのかな、という印象です。

      一応、.CopyFromRecordsetでも、SELECTに続くフィールド名の並びを変えることで取得項目の順番を調整することはできますが、ずばっとペーストできる反面、もうちょっと柔軟に使いたいとはわたしも思うところです。そんなとき、「設定」や「work」という名前の「裏で動く用」シートを一枚用意しておいて、

      Sub 読込()
  Dim i As Integer
  
  With Sheets("work") 'workという名前のシートを作っておく
    Call DBconnect(True) 'DB接続
  
    strSQL = "SELECT xxx FROM xxx"
    adoRs.Open strSQL, adoCn 'SQLを実行して対象をRecordSetへ
    .Range("A1:E1000").ClearContents '前のデータクリア（work）
    .Range("A1").CopyFromRecordset adoRs 'セルへ出力（work）
    
    Call DBcut_off(True) 'DB切断
    
    For i = 1 To 1000
      If .Cells(i, 1) = "" Then Exit For '空白になったら終了
      'アクティブシートにworkの内容を転記
      Cells(i, 3) = .Cells(i, 1)
      Cells(i, 1) = .Cells(i, 2)
      Cells(i, 2) = .Cells(i, 3)
    Next i
  End With
End Sub

      「work」というシートに一旦吐き出しておいて、メインのアクティブシートに好きに持ってくる、ということができます。この例ではただ並び替えてるだけなので効率的とは言い難いですが、実際はSQLで吐き出されたデータを、Excel側で更に条件を絞ったり計算しながら転記してきたりなど、使い方によっては有用です。

      エラーの根本的な解決にはなりませんが、参考にして頂ければ幸いです。

  6. ARIES10 より:
    2013年12月6日 11:21 AM

    おはようございます。

    大変申し訳ございません。

    スタート行の指定である
    i = 10
    をコピーし損ねていました。

    また、途中の試行錯誤で初期化のためにi = 0と
    していることもありましたが、0行目はエクセルに
    ないので、もしここを試しにi=1にでもしていたら
    1行目から出力が行われ、間違いに気づけたのですが…

    コピペ用のソースをテキスト形式でまで載せて
    頂いているのにそれをコピペしそこなうという
    残念な私で大変恐縮です。
    管理人様の貴重なお時間を頂いてしまいごめんなさい。

    ご例示いただいたworkシートの活用という概念を
    今後活用することで、ご容赦ください。

    失礼いたしました。

    これから、追記していただいた内容にトライしてみようと
    思います！

    返信
    • *you より:
      2013年12月6日 3:39 PM

      ARIES10さん、こんにちは。

      iがゼロでしたか！気づいてみるととても小さなことでも、原因が分からないときは本当に迷宮な気分なのはよく分かりますので、問題ありませんよ。なによりご自分で解決されて良かったです。ExcelVBAは初めてとのことでしたが、さすが他言語の経験がある方はコツを掴むのが早いですね。

      追記のほうのコメント、見てみたら該当記事のコメント欄が閉じていました(;´Д`) そちらを直して移させて頂きましたのでご了承ください。


コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください。

コメントは承認制ですので、反映までしばらくお待ち下さい。（稀にスパムの誤判定にて届かないこともあるようですので、必要な際はお問い合わせからお願い致します。）

YouTubeでQ&Aコンテンツを企画しています

運営しているYouTubeチャンネルで、ご相談やご質問を募集しています。動画のコメントやお問い合わせページからお気軽にご相談をお寄せください。