ExcelVBAとAccessの連携 第6回 トランザクション処理
ながいことやっております、ExcelからAccessのデータベースを利用しちゃおうシリーズ。遂に今回で最終回です。実際の処理は前回までで出来るのですが、実用性を高めようという内容です。
関連記事
- 第1回 Excelからデータベースへの接続
- 第2回 テーブル設計とシート&コードの準備
- 第3回 SQLを使った読み書きの処理
- 第4回 条件を絞ってデータを読み込む
- 第5回 レコードの更新・削除
- 第6回 トランザクション処理 ←NOW!
- 番外1 リファクタリングしたコード
- 番外2-1 プレースホルダを使ったSQL実行
- 番外2-2 続・プレースホルダを使ったSQL実行(汎用化)
トランザクションとは
まずはこちらの引用を。
トランザクションとは : IT用語辞典より抜粋
- 関連する複数の処理を一つの処理単位としてまとめたもの
- トランザクションとして管理された処理は「すべて成功」か「すべて失敗」のいずれかであることが保証される
- どちらか一方が失敗したらもう片方も失敗させ、どちらも成功したときに初めて全体を成功と評価する
DBを使ったシステムの場合、複数の処理を伴うことが多く、何らかのエラーで途中で処理が止まってしまうと不具合が出る可能性が多々あります。
この図のように、ハンパな処理がDB上に存在してしまうことになります。そこでトランザクション処理を実装すると、
全て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)
書籍化なども経て、さらにスマートに書き直したコードもまとめました。
ExcelVBAに興味をお持ちの方は、こちらの記事もどうぞ!
- これからExcelのマクロを始めたいという方に!簡単な練習問題作りました。
- 私がExcelVBAでよく使う便利なコード・スニペットまとめ
- プログラム初心者さんへ贈る、エラーが起きたら試してみて欲しいこと
- ExcelVBAのクラスモジュールって何?という人向けの使い方まとめ
書籍を執筆しています。
12件のコメント
はじめまして。
楽しく拝見させていただきました!
なかなかexcelとaccessのDB連携についてわかりやすくかかれたサイトがなかったので非常に助かりました。
デザインもおしゃれで見やすかったです。
今後はフォームやコントロールを使ったVBAプログラムの記事があったら・・・ということを期待しています笑
hiroさん、はじめまして!コメントありがとうございます。
有難いお言葉、本当に本当に感無量です。まとめてみて良かったです…。・゚・(ノД`)・゚・。
リクエストもありがとうございますー!書いてみたいネタはいくつかあるのですが、現在、仕事で使っているOfficeが未だ2003のため、個人でもまだ(メンテとかのために)2003から抜け出せずにいるんです。今時2003を使ってFormの説明しても2007以降で見た目も機能も違うんじゃないのかな、という気持ちからなかなか書けずにいまして…。
でも、来春にはOfficeを2013へ一斉バージョンアップする予定で、それに慣れたらそっち系の記事も書けたらなと思っています!2003→2013への移行不具合は今からハラハラしておりますが…(;´Д`)
こんにちは。
大変ためになりました。
頭の中でわかっていることを、よくわかっていない人の視点にたって噛み砕いて説明することは難しいし、手間のかかることだとおもいます。
でもそれを実際にしてくださっていることで、私のような者はとてもとっつきやすくエクセルVBA&アクセスに取り組めるようになります。
今エクセルVBAとアクセスを使ってやりたいことがあり
質問サイトで質問したところご紹介頂いてここに来ました。
大変感謝しています。
ぜひ一言お礼を言いたくて書き込ませていただきました。
今後も頑張ってください!
ARIES10さん、コメントありがとうございます!
そのようなお言葉をいただけて、本当に光栄の極みです…!この連携については私もかなり試行錯誤した結果ですので、こんな私の方法がお役に立ててとっても嬉しいです。お言葉に甘えて、今後も頑張らせて頂きます!ありがとうございました!
こんばんは。
本日夕方よりご例示いただいているツイートシステムを
作ってみました。
記事を読むだけでなく、自分のエクセルでの実装も
無事トランザクション処理まですべて行えました!
本当にわかりやすかったです。
改めてありがとうございました。
もしよかったら、なのですが、
私はご例示いただいたVBAを応用して、在庫管理テーブルの読み込み、編集、更新をしようとしているのですが、
今回のVBAと同じように読み込んだ後、違う点として
編集するレコードが複数の場合があります。
このときに、読み込んだレコード達のうち、
追加で「更新対象」という列のチェックボックスにチェックが入っているものはすべて更新していく、というような
ループ処理も可能なのでしょうか。
もし簡単にご教示いただけるようでしたらご教示いただけると大変うれしいです。もし簡単でなかったりお忙しいようでしたら、大丈夫です。
ARIES10さん、こんにちは。
実装報告して頂けたのは初めてなので嬉しいですー!ちゃんと動いて良かった!
わたしが書いた記事では、編集(UPDATE文)の後、SELECT文にて再読み込みするということを「更新」と表現したつもりでしたが、その意味でよろしいでしょうか?
管理者注:この記事を書いた当時名称を混同しておりまして、SELECTでのデータ読込を画面更新のつもりで「更新」、UPDATEのことを「編集」と書いてしまったため質問者さんにご迷惑をおかけしました。現在は記事のほうは修正してあります。
ループを使って一部だけを再読み込みすることは可能とは思いますがおそらく結構面倒です。複数箇所をちょっとずつ再読み込みするよりは、シート上のデータを全て再描写してしまったほうが簡単で結果も同じなのではないのかな、と思います。
説明では、分かりやすくするために編集(UPDATE文)した後、「更新」ボタンを押さないとシートに反映されないようになっていますが、
のように、編集プロシージャの最後で更新プロシージャ(更新ボタンを押すのと同じ動きをするところ)を呼び出すようにしておけば、編集直後にシートが自動で更新されるのでストレスなく操作できるようになると思いますが、いかがでしょうか。ご期待に添えれば良いのですが…。
おはようございます。
早速お返事頂けて感激しております。
ご親切にありがとうございます!
>わたしが書いた記事では、編集(UPDATE文)の後、SELECT文にて再読み込みするということを「更新」と表現したつもりでしたが、その意味でよろしいでしょうか?
申し訳ございません。
その「更新」のことではありません。
説明が下手で失礼いたしました。
記事内で、「編集」で行っている処理を複数レコード分一気にやりたいというものでした。
No. アイテム サイズ 在庫 対象
1 服A S 3
2 服A M 4
3 服A L 5
4 服B L 2
あるアイテムの服AのSとMが一着ずつ売れた時、
服Aと打ち込んで「更新」ボタンをおせば
No.1と2と3が表示されると思います。
このとき、No.1と2を更新したいときに
編集ボタンを押して、一つずつ更新したい値を
入れるのではなく、明細上で数値を書き換え、
たとえば対象という右端の列に「1」と記入した
レコードのもののみをLOOPで見つけて「編集」処理を
やっていくということです。
ちょっと仕様の違いがご例示のVBAとは異なってきてしまうでしょうか?
すみません、VBAを始めたばかりでそのあたりの感覚がまだなくて。m(_ _)m
よかったら、ご返信ください。
ああ、やはりUPDATEのことでしたね…!こちらこそすみません。
上記のこと、問題なく実装できます。むしろ、このようなたくさんの処理をすることこそExcel&Accessの醍醐味(?)ですよね。
ちょっとコメント欄では説明するスペースが小さすぎますので、記事に追記させて頂きますね。
続けてすみません。
ご例示の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
ええと、まず、複数のUPDATEについては第5回の記事に追記しましたので、ご参照ください。
エラーについては、申し訳ありませんが、こちらでは再現することができませんでした。そこで止まるのならばSQLの実行は通っているはずですし、.CopyFromRecordsetでデータが取り出せるならこちらの形式でも問題ないと思うのですが…。Cellsの中にiを使えることは間違いないので、レコードからフィールドを取り出すのがうまくいっていないのかな、という印象です。
一応、.CopyFromRecordsetでも、SELECTに続くフィールド名の並びを変えることで取得項目の順番を調整することはできますが、ずばっとペーストできる反面、もうちょっと柔軟に使いたいとはわたしも思うところです。そんなとき、「設定」や「work」という名前の「裏で動く用」シートを一枚用意しておいて、
「work」というシートに一旦吐き出しておいて、メインのアクティブシートに好きに持ってくる、ということができます。この例ではただ並び替えてるだけなので効率的とは言い難いですが、実際はSQLで吐き出されたデータを、Excel側で更に条件を絞ったり計算しながら転記してきたりなど、使い方によっては有用です。
エラーの根本的な解決にはなりませんが、参考にして頂ければ幸いです。
おはようございます。
大変申し訳ございません。
スタート行の指定である
i = 10
をコピーし損ねていました。
また、途中の試行錯誤で初期化のためにi = 0と
していることもありましたが、0行目はエクセルに
ないので、もしここを試しにi=1にでもしていたら
1行目から出力が行われ、間違いに気づけたのですが…
コピペ用のソースをテキスト形式でまで載せて
頂いているのにそれをコピペしそこなうという
残念な私で大変恐縮です。
管理人様の貴重なお時間を頂いてしまいごめんなさい。
ご例示いただいたworkシートの活用という概念を
今後活用することで、ご容赦ください。
失礼いたしました。
これから、追記していただいた内容にトライしてみようと
思います!
ARIES10さん、こんにちは。
iがゼロでしたか!気づいてみるととても小さなことでも、原因が分からないときは本当に迷宮な気分なのはよく分かりますので、問題ありませんよ。なによりご自分で解決されて良かったです。ExcelVBAは初めてとのことでしたが、さすが他言語の経験がある方はコツを掴むのが早いですね。
追記のほうのコメント、見てみたら該当記事のコメント欄が閉じていました(;´Д`) そちらを直して移させて頂きましたのでご了承ください。
コメントは承認制ですので、反映までしばらくお待ち下さい。(稀にスパムの誤判定にて届かないこともあるようですので、必要な際はお問い合わせからお願い致します。)
YouTubeでQ&Aコンテンツを企画しています
運営しているYouTubeチャンネルで、ご相談やご質問を募集しています。動画のコメントやお問い合わせページからお気軽にご相談をお寄せください。