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

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

関連記事

• 第1回 Excelからデータベースへの接続
• 第2回 テーブル設計とシート&コードの準備
• 第3回 SQLを使った読み書きの処理 ←NOW!
• 第4回 条件を絞ってデータを読み込む
• 第5回 レコードの更新・削除
• 第6回 トランザクション処理
• 番外1 リファクタリングしたコード
• 番外2-1 プレースホルダを使ったSQL実行
• 番外2-2 続・プレースホルダを使ったSQL実行（汎用化）

プロフィールの読み込み

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

データを抽出するSQL

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

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

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

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

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

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

SQLをVBAで扱う

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

整形

今回の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でしてあるので、結構シンプルですよね。

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

ツイートの書き込み

次は、書き込み。

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

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

データを書き込むSQL

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

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

整形

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

書き込みの場合は、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

先ほど変数nはadoRs.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

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

ツイートの読み込み

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

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

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

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

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

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

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

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

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

• VB SQL テーブルの結合
• SQLのJOIN文を視覚的に理解する | IDEA*IDEA
• 地獄のようによくわかるSQLテーブル結合 – こせきの技術日記

これで抽出は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

使いまわせて便利です。

　

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

今後の予定

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

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

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

続き書きました！

• ExcelVBAとAccessの連携 第4回 条件を絞ってデータを読み込む