2013
7
04

ExcelVBAとAccessの連携 第5回 レコードの更新・削除

お久しぶりの、ExcelからAccessのデータベースをごにょごにょしちゃう記事の続きです。登録したツイートを更新したり削除する機能をつけてみます。(実際のtwitterは更新機能はないですが)


関連記事

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

下準備

レコードセットオブジェクトの有無

前回までで紹介してきたコードでは、SQL文を書いた後に

という部分がありました。これは、すでに存在しているレコードを開くときに使うもので、今回紹介する、更新・削除の操作には使わないものなんです。

前に書いたこの接続・切断のコードでは、adoRsを使わない処理の場合、レコードセットをクローズしようとするとき(9行目の部分)に、「使ってないのにクローズできないよ!」とエラーになってしまうのです。

なので、下記のように書き換えます。

flgというBoolean変数(TrueかFalseの二択)をつくり、Trueのときだけレコードセットオブジェクトを使用する、という形にします。

そして各処理から呼び出す際に、

と書けばレコードセットオブジェクトを使用できて、

と書けばレコードセットオブジェクトを使用しない、という使い分けが出来るようになります。

引数(カッコ内のTrue/False)は省略できないので、前回までに書いたコードの接続・切断の呼び出し部分も全てこのように変更してください。

ボタンの追加

130704-01

更新と削除用のボタンを追加します。

モジュールに新たに更新・削除用のプロシージャを追加して、それぞれさっき作ったボタンから起動できるようにしておきます。

更新

データを更新するSQL

130704-02

基本形はこんな感じ。内容1,内容2,内容3…のようにカンマで区切って複数更新もできます。修正したい内容は変数tw_strに予め入れておくとして、問題は「どのナンバーか」を特定するところです。番号を入力してもらうなど方法はいくつかありますが、今回は、更新したいツイートNoのセルを選択した状態で更新ボタンを押してもらうというルールにしてみます。

130704-03

テーブル名などを当てはめて、変数に置き換えたSQL文は、こんな感じ。

コード

4~7行目は、選択されているセル(アクティブセル)が空白または数値じゃなかったら処理を終了する、ということを書いています。9行目では更新するテキストの入力要求を行い、10行目でそれが空、またはキャンセル(False)なら処理を終了します。

ナンバーとテキスト両方が正常に取得できたら、DBへ接続、SQL文の実行へ進みます。このコードではレコードセットオブジェクト(adoRs)を使っていないので、接続・切断の引数はFalseにしておきます。

130704-04

では実際に5番を選択して更新ボタンを押して、修正してみましょう。

130704-05

正常に終了したら、読み込みしてみてください。(データ更新しかしてないので、読込ボタンを押さないと画面に出てこないのでご注意。)うまくできたでしょうか?

削除

データを削除するSQL

130704-06

基本形。WHERE以下の条件を指定しないと、テーブルの中身を全て削除します。

コード

更新のときとそんなに変わらないので図解は省きましたが、ポイントとしてはハイライトしてある7行目でしょうか。データの削除前にはユーザーに確認を出すのが親切だと思います。MsgBox("説明文", vbOKCancel)では、押されたボタンがOKなら1,Cancelなら2が返ってきます。✕を押されることもあるので、1(OK)以外なら終了、という書き方をしています。

他にも、「はい」「いいえ」なども出せますので、調べてみてください。
130704-07

実際に5番を選択して削除ボタンを押してみます。

130704-08

読込してみましょう。出てこなければ成功です。

あと1回です

少しずつ書き進めていましたが、次回、トランザクション実装を紹介して終了の予定です。よろしければご期待ください。

追記

複数の処理をループさせるには、という質問を頂いたので書いてみました。

130704-09

サンプルはこのような形。ご提案頂いたものは、「この行(レコード)をUPDATEする」ということをどこかに印をつけておいてループできないか、というものでした。

もちろんそれでも実装可能なのですが、1行全部UPDATEしていくよりは、変更されたセルの色を変えておいて、セル(フィールド)単位でピンポイントにUPDATEしたほうが無駄が少ないんじゃないかな、という構想で書いてみます。

変更されたセルを赤字に

まずはセルが変更されたら走るコード。

任意シートのコード画面

全てのセルに適用されても困るので、範囲を決めてそれ以外は終了するようにしています。IDはそもそも変えてはいけませんので範囲外にします。

コードを書く場所はこちらをご参照ください。

Worksheet_Changeは便利ですが、どんな場面でも変更があると走ってしまうので煩わしいということもあります。特に、SELECT文で読込をしたいだけなのに走ってしまうと非常にアレなので、そういう時は

のようにイベントを一時停止することができます。Trueに戻すのをお忘れなく!

赤字部分だけUPDATEする

セルが特定できたら、今度は対応するテーブル名とフィールド名を特定しなければなりません。ここで、配列というものを使ってみます。

130704-10

このように書くと、f_ary(0)ならf_item、f_ary(1)ならf_size、となります。今回の例はテーブル名は全て一緒にしてますが、もちろん違う名前のテーブルが混ざっても構いません。(実務では複数のテーブルを使うことが多いと思うので)

Module

赤文字の部分のみ処理するようにIf文を使い、「該当セルの列番号-3」がちょうど対応した配列番号になるのを利用します。あとはセル内容などを使ってUPDATE文を成形し、終わったらフォント色を戻しています。

また、このように複数の処理をまとめて実行するときほどトランザクションは有用なので、そちらも盛り込んでみました。(トランザクションがどういうものか、ということについては第6回をご参照ください。)

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

公開日:2013/07/04
更新日:2014/04/29


7件のコメント

  • ARIES10
    2013年12月6日 1:25 PM

    こんにちは。

    今回はご例示頂いたものではなく、私が既に流用して
    作らせて頂いた在庫管理システムそのものを書き加える形で
    実践してみました。

    12行目から明細がスタートして3列目から6列目までを
    LOOPさせると明細と合致するのが私の作ったものです。
    結果としては、LOOPさせているはずなのですが、
    必ず12行目の6列目で赤字かどうかを確認するIF文の
    中に入ってしまいました。

    色々と試したところ、「読込」ボタンを押す時に
    前回の内容を消す関数がclearcontentsの場合は
    数式と文字だけを消すようで、表示されていないけど
    セルの文字色は赤として残っていたようです。
    これをclearに変えるとうまくいきました。
    そのかわり明細の罫線も消えてしまったので
    ちょっと寂しかったですけど。笑

    今ぶつかっているのは、strSQLにデータを吸い出して
    セットしているところにポインタをおいて中身を見ても
    ちゃんと「UPFATE テーブル名 SET 列名=赤字セルの値 WHERE ID=赤字セル行のID」となっているのですが、その後の
    「adoCn.Execute strSQL ‘実行」に来ると
    「1つ以上の必要なパラメータの値が設定されていません」
    と出てしまっていることです。

    ちょっとこちらはまだよくわかっていませんが一度お昼を食べさせていただきます。

    これが成功したらかなりスマートな更新処理が完成しそうでワクワクしています。

    早速の追記、本当にありがとうございます。

  • ARIES10
    2013年12月6日 3:38 PM

    完成しました。

    先ほどのは、列名が微妙に
    違っていました。ケアレスですみません。

    かなり実用化に近づけました!

    本当にありがとうございました。

    今回ご構想いただいた内容はまさに求めて
    いたものでした。そのような無駄のなさにまで
    ご配慮いただきありがとうございました!

    • *you
      2013年12月6日 5:34 PM

      おめでとうございますー(*´∀`*)

      確かに、.ClearContentsだと値しか消えないですもんね。かと言って.Clearで全ての書式設定が消えるのも困りますよね。罫線消えちゃわないようにするなら、読込のところにも

      値を消した後フォントの色を戻すコードも入れておくと安心かもです。

      おつかれさまでした!がんばってくださいー!

  • ARIES10
    2013年12月6日 8:25 PM

    こんばんは。

    本当に色々とありがとうございました。
    (罫線についてもさらなるアドバイスを頂きありがとうございます!)

    基礎的なVBプログラムの記述の仕方は少し知っていても
    エクセルVBAの場合、何を開いてどこに書けばよいのか
    こちらのサイトに来るまではよく知らない状態でした。

    そこからモジュールやユーザーフォームがあり、
    ボタンを押すと処理が走るための紐付けの方法があり、
    フォームからのデータの取得の方法があり、
    エクセル内で完結せずにアクセスにまで接続し、
    と、最初のとっつきにくかった部分が、*youさんの
    おかげで全部理解できました。

    細かい処理の部分や関数などはまだまだ知らないことも
    多いですが、そういうものは逆引き辞典などで
    今後勉強していこうと思います。

    自分で在庫管理エクセルVBAが作れるなんて先週までは
    自信がありませんでしたが、今は作ったこのVBAを
    もう少しあれとこれを改良してみよう、
    などとワクワクしています。

    *youさんのおかげです。
    本当にありがとうございました。

    P.S.子育て中のママさんなんですね。
    私も5歳と3歳の男の子のパパです。
    お互いに子育て頑張りましょう!

    これからしばらくVBAの問題練習やその他の
    面白そうな記事だらけのこのサイトで色々と
    拝読させていただきます~。

    それでは失礼いたします。

    • *you
      2013年12月7日 12:05 AM

      大変嬉しいお言葉、ありがとうございます。・゚・(ノД`)・゚・。

      このブログには、わたしが数年かけて覚えたExcelVBAのノウハウを詰め込んでいるつもりですので、これを利用してこんなに早くマスターしてくださる方が現れたということが、今まで書いたものが報われた気がしてとても嬉しいです…!

      Excelは、シートやセル、フォームなど多くの媒体を持っているぶん走らせ方を覚えるのがちょっと大変ですが、そこさえクリアしてしまえば先が見えてきますよね。ワクワクするという気持ち、とても良くわかります。

      他の言語(Web系)だったり、かなり一貫性のないジャンルを書いているブログですが、読んで頂けたらこの上ない喜びです!同じ子持ちプログラマとして、一緒にがんばれたら嬉しいですー(*´∀`*)

  • ARIES10
    2013年12月9日 5:28 PM

    こんばんは。

    今日も読ませて頂きましたが、
    人気の記事を見てびっくりしました。

    大学時代にフォトショとイラレを使う講義がありつつ
    フォトショには慣れたもののイラレには慣れず。
    その後も近寄りがたくてイラレはそのまま、
    フォトショの限られた機能のみ使う感じで。

    そして先週妻に携帯を落とされ私のiphoneには
    ヒビが入っています。
    操作も表示もすべて可能でロックもしていないとはいえ、
    なんて有用な記事だらけのサイトなんでしょうか!
    と驚きの連続でした。

    色々と学ばせて頂きます!m(_ _)m

    • *you
      2013年12月10日 10:27 AM

      ARIES10さん、こんにちは。
      すみません、笑ってしまいましたw たまたま状況に合致した記事が多かっただけとは言え、運命的ですねww
      たくさん読んで頂いてありがとうございます(*´ω`*)

コメントを残す




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


back to top