2013
3
12

ExcelVBAとAccessの連携 第3回 SQLを使った読み書きの処理

前回までの記事で、Accessのテーブルを作成して、ExcelVBAから接続するところまでできました。では、実際にデータベースに書き込んだり、データを取り出してセルに出力してみます。


関連記事

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

プロフィールの読み込み

130312-01

前回作ったシートの、D3セルに対応したプロフィールをE5~E7セルに出力してみます。

データを抽出するSQL

130312-02

これが超基本的な形。フィールドは、複数の場合はフィールド1, フィールド2… と続けます。

130312-03

実際に作ったテーブル名などを当てはめてみました。「*」は、「すべて」という意味になります。t_mainテーブルのフィールドを全て抽出しますよ、という感じです。これを実行すると、

130312-04

結果はこのようになります。全部出てきます。でも、欲しいのは、特定のアカウントのプロフィールですよね。

130312-05

条件を指定するには、先程のSELECT文にWHEREを追加します。

130312-06

特定の条件を指定してみると、こんな感じ。これなら、f_idというフィールドが'ateitexe'という文字列のレコードを全て抽出します。文字列は、「’(シングルクォーテーション)」で括るのがルールです。

「”(ダブルクォーテーション)」でも良いらしいのですが、後でこのSQL文をExcelVBAのほうで文字列として扱うので、VBAでは「”」、SQLでは「’」と使い分けるために、ここでは「’」にしておいてください。
130312-07

これで実行すると、指定したもののレコードだけ抽出することができます!

SQLをVBAで扱う

130312-08

書くべきSQL文が定まったところで、これをVBAで文字列として変数に格納し、前回書いておいた接続を使って実行します。すると、変数adoRSにSQLの実行結果が入ります。

整形

130312-09

今回のSQL文は短いので1列で書いてしまっても大丈夫ではありますが、今後のことも考えて、VBAに書くときは文節ごとに改行しておくと、あとで見易くなります。

また、さきほどのコードの条件を、直接の文字列から変数にしておかないとならないので、ateitexeの部分をRange("D3")に変更します。

コード

さて、今まで解説したことを書き起こすと、こんな感じになります。

宣言などは事前にPrivateでしてあるので、結構シンプルですよね。

130312-10

ボタンを押すと、Accessのテーブルに書いておいたデータがExcelのセルに入ります。いかがでしょうか?D3セルの内容をateitexe_wpateitexe_vbaに変えて、試してみてください。(存在しないものを指定するとエラーになります)

ツイートの書き込み

130312-11

次は、書き込み。

130312-12

書き込みの構文を書く前に、その都度違うf_noの数値を確定させなければなりません。SELECT文でf_noの最大値を取得し、+1したものを変数nとしておきます。

adoRs.Fields(0)は1列目という意味。MAXは集計関数なので通常のadoRs!f_noという形では取り出せないみたいですのでご注意。

追記:ここではf_noの値を自分で決める形で書いてしまいましたが、前回のAccess側での設計時にf_noを「オートナンバー型」にしておけば、このあたりは必要ありません。

データを書き込むSQL

130312-13

新しいデータを書き込む基本形は、こんな感じ。

130312-14

当てはめてみました。f_idf_contentは文字列なのでシングルクォーテーションで括ります。NOW()というのは現在の日時を取得するAccessの関数です。日付だけで良い場合はDATE()、時間だけならTIME()で取得できます。

整形

130312-15

先ほどと同じように、VBA用に文節ごとに改行して、変数部分は置き換えます。

130312-16

書き込みの場合は、VBAでこのように書いてSQLを実行します。

コード

解説したことを書き起こしてみます。

先ほど変数nadoRs.Fields(0)+1で、と書きましたが、まだデータが何も入っていなかった場合も条件分けして書いておきます。変数nはPrivate宣言していないので、このプロシージャ内で使えるよう、Dimで宣言します。

また、書き込みの場合は正しく実行されたか終了がわかりにくいので、メッセージボックス(24行目)を出してあげると親切です。

試しにいろんなアカウントで書き込んでみてください!

追記:f_noをオートナンバー型にした場合

ナンバーをお任せにしちゃうと、こんなに短く書けます。勝手につけてくれる代わりに、後で自分で変更できないという特徴もありますので、用途に合わせてご検討ください。

この方法は、現状だとDB接続・切断でエラーが出てしまいます。その原因と、回避するための方法として(False)という記述をしますよー、ということを第5回の記事で説明しているのでそちらをご覧ください。

ツイートの読み込み

130312-17

(今回の記事では)最後となる、ツイート読み込みの処理を書きます。プロフィールを抽出したときと同じようにSELECT文をつくるのですが、今回はふたつのテーブルからの抽出になるので、ちょっと複雑になります。

複数テーブルからデータを抽出するSQL

130312-18

SELECT~FROM~は同じなのですが、ふたつのテーブルを結合するJOIN、結合条件のONなどが入ってきて、ちょっと難しいですね。

前回はテーブルがひとつだったので、フィールド名は簡単で良かったのですが、複数のテーブルを扱う場合は、「テーブル名.フィールド名」という指定方法になります。
130312-19

今回の目的に当てはめて、まず一番上の行から見ていきます。

ふたつのテーブルの全てのフィールドが欲しいわけではないので、抽出したい順番に、欲しいフィールド名をひとつずつ書き出していきます。

130312-20

お互いのテーブルで同じ内容のフィールドがあるはずなので(そのように設計しておきます)、そこをON(接続条件)として書き、ふたつのテーブルをJOINで結合させます。

JOINには種類がいくつかあって、右テーブルのデータの有無にかかわらず抽出するLEFT JOINと、両方のテーブルにデータが存在するレコードだけを抽出するINNER JOINが、主に使われているようです。

JOINをはさんで左側に置いたテーブルが基準になるので、テーブルを書いた順番によって実行結果が異なります。どんなデータを抽出したいのかをよく考えて、テーブルを結合させるのが重要です。

詳しくは、こちらのサイトが参考になります!

今回の場合、試しにt_mainテーブルに存在しないアカウントで書き込みを行うと、LEFT JOINだとそのレコードは抽出されますが、INNER JOINだと抽出されません。
130312-21

これで抽出はOKですが、twitterのタイムラインっぽく、書き込みの新しいものを上にくるように並べ替えます。並び替えの基準となるフィールドを指定して、昇順はASC、降順はDESCと書きます。(昇順は省略可)

今回の場合は、t_tweet.f_dayというフィールドを基準に降順にします。

コード

以上のことをまとめると、このようになります。

12行目で、貼り付けるセルのスタート位置を指定して一気に出力、という作業を行っています。この方法だと書式設定がデフォルトに戻ってしまうケースがあるので、13行目で書式を設定し直しています。

ちなみに、別の貼り付け方法として12行目をこちらのコードに差し替えてもOKです。これは書式設定に影響を与えないので上記の13行目は不要になります。

この場合は、冒頭で変数iを整数型でDim宣言してください。

追記

レコードを1行ずつ読んで行く方式だと、データが空の場合にエラーになってしまいます。読込の前に、データが空だったら終了するコードも書いておきましょう。

ちなみに、「= True」って省略できるらしいです。知らなかった(;´Д`)

130312-22

ボタンを押すと、書き込んだツイートが取得されます。いかがでしょうか?これはテーブルをLEFT JOINで結合したイメージなので、良かったらINNER JOINでも試してみてください!

今後の予定

長かったですね…。基本機能はここまでで大体いいかなーと思っているので、あと考えているのは、

  • レコードの削除・更新
  • 更に条件を絞っての読み込み
  • トランザクションの実装

あたりかなと思っています。一応基本機能は終わったことですし、これ系の記事がこれ以上続くとExcel興味ない方が離れてしまわないか不安なのでw、他のジャンルの記事も挟みながらゆっくり書いていこうかと思っています。宜しければご期待くださいませ。

続き書きました!

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

公開日:2013/03/12
更新日:2014/04/29


コメントを残す




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


back to top