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

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")に変更します。

コード

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

Sub DBprf() 'プロフ表示
  Call DBconnect 'DB接続

  'プロフィール読み込みのSQL
  strSQL = _
    "SELECT * " & _
    "FROM t_main " & _
    "WHERE f_id = '" & Range("D3") & "'"
  adoRs.Open strSQL, adoCn 'SQLを実行して対象をRecordSetへ
  Range("E5:E7").ClearContents '前のデータクリア
  Range("E5") = adoRs!f_name 'セルへ出力
  Range("E6") = adoRs!f_notes
  Range("E7") = adoRs!f_url

  Call DBcut_off 'DB切断
End Sub

宣言などは事前に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を実行します。

コード

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

Sub DBwrite() '書き込み
  Dim n As Integer

  Call DBconnect 'DB接続

  'Noの最大値を調べるSQL
  strSQL = _
    "SELECT MAX(f_no) " & _
    "FROM t_tweet"
  adoRs.Open strSQL, adoCn 'SQLを実行して対象をRecordSetへ

  If IsNull(adoRs.Fields(0)) Then 'レコードが1件も無ければ
    n = 1 'ナンバーは1
  Else
    n = adoRs.Fields(0) + 1 'それ以外は最大値+1
  End If

  'ツイート書き込みのSQL
  strSQL = _
    "INSERT INTO t_tweet(f_no, f_id, f_day, f_content) " & _
    "VALUES(" & n & ", '" & Range("D3") & "', NOW(), '" & Range("E3") & "')"
  adoCn.Execute strSQL '書き込み実行

  MsgBox "正常に書き込み完了しました"

  Call DBcut_off 'DB切断
End Sub

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

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

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

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

Sub DBwrite() '書き込み

  Call DBconnect(False) 'DB接続

  'ツイート書き込みのSQL
  strSQL = _
    "INSERT INTO t_tweet(f_id, f_day, f_content) " & _
    "VALUES('" & Range("D3") & "', NOW(), '" & Range("E3") & "')"
  adoCn.Execute strSQL '書き込み実行

  MsgBox "正常に書き込み完了しました"

  Call DBcut_off(False) 'DB切断
End Sub

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

この方法は、現状だと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というフィールドを基準に降順にします。

コード

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

Sub DBtweet() 'ツイート読み込み
  Call DBconnect 'DB接続

  'SQL文の作成
  strSQL = _
    "SELECT t_tweet.f_no, t_tweet.f_id, t_main.f_name, t_tweet.f_content, t_tweet.f_day " & _
    "FROM t_tweet LEFT JOIN t_main " & _
    "ON t_tweet.f_id = t_main.f_id " & _
    "ORDER BY t_tweet.f_day DESC"
  adoRs.Open strSQL, adoCn 'SQLを実行して対象をRecordSetへ
  Range("B10:F26").ClearContents '前のデータクリア
  Range("B10").CopyFromRecordset adoRs 'セルへ出力
  Range("F10:F26").NumberFormatLocal = "yyyy/m/d h:mm;@" '書式設定

  Call DBcut_off 'DB切断
End Sub

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

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

  'データが空の場合は終了する
  If adoRS.BOF = True And adoRS.EOF = True Then
    Call DBcut_off 'DB切断
    Exit Sub
  End If

  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宣言してください。

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

2017/5/19追記

更に、フィールド名を変数にすることも。

  'データが空の場合は終了する
  If adoRS.BOF = True And adoRS.EOF = True Then
    Call DBcut_off 'DB切断
    Exit Sub
  End If

  Dim outputCell as Range: outputCell = Range("A1") '基点セル指定
  Dim row As Integer: row = outputCell.Row '行取得
  Dim col As Integer: col = outputCell.Column '列取得
  Dim field As Object, i As Integer
  Do Until adoRs.EOF 'レコードセットが終了するまで処理を繰り返す
    i = 0
    For Each field In adoRs.Fields 'フィールドの数だけ繰り返す
      Cells(row, col + i) = adoRs(field.Name)
      i = i + 1
    Next
    row = row + 1 '行をカウントアップする
    adoRs.MoveNext '次のレコードに移動する
  Loop

使いまわせて便利です。

 

130312-22

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

今後の予定

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

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

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

続き書きました!

公開日:2013/03/12
更新日:2017/05/19

2件のコメント

  1. kk より:

    ExcelVBAとaccessの連携第3回のところでプロフィールの表示以降ができません。コードを示します。

    (管理者により省略させていただきました)

    Excel、Accessともに2010を使っています。よろしくお願い致します。

    • *you より:

      コード(長いので省略させていただきました)を提示されただけではなんともわかりませんが、ファイルのパスを指定するところPrivate Const DBpath ...と、コネクションをオープンするところadoCn.Open ...の頭に「’(コメントアウト)」がついているのは気になりました。ここがコメントアウトになっていたら、Accessファイルに接続できないのでは?

      もしかしてコメントアウトをご存知なくて、プログラミングを初めたばかりとかでしたら(違ってたらすみません)、いきなりDBとの接続は厳しいので、こちらから一通り基礎を学習してからのほうが良いと思います。

      また、質問の前に、こちらの記事もご一読ください。


コメントを残す

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

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

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

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

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