2013
9
18

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

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


関連記事

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

トランザクションとは

まずはこちらの引用を。

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

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

130918-1

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

130918-2

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

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

まとめ

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

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

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

あとがき

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

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

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

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

  • このエントリーをはてなブックマークに追加
  • follow us in feedly 618
  • RSSを登録

公開日:2013/09/18
更新日:2014/04/29


12件のコメント

  • 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への移行不具合は今からハラハラしておりますが…(;´Д`)

  • ARIES10
    2013年11月23日 12:17 PM

    こんにちは。

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

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

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

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

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

    • *you
      2013年11月25日 11:13 AM

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

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

  • ARIES10
    2013年12月4日 11:57 PM

    こんばんは。

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

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

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

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

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

    • *you
      2013年12月5日 10:56 AM

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

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

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

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

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

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

  • ARIES10
    2013年12月5日 11:48 AM

    おはようございます。

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

    >わたしが書いた記事では、編集(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

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

    • *you
      2013年12月5日 12:50 PM

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

  • 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」という名前の「裏で動く用」シートを一枚用意しておいて、

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

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

  • 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は初めてとのことでしたが、さすが他言語の経験がある方はコツを掴むのが早いですね。

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

コメントを残す




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


back to top