ExcelVBAとAccessの連携第2回テーブル設計とシート&コードの準備

2013/03/08
2015/11/16
MS Office
Access, Excel, VBA

さて、前回「具体的なモノをつくってみるぞー」という言葉で締めさせて頂いたわけですが、ExcelVBAとAccessデータベースを使った簡単なアプリケーションを作ってみます。是非一緒に作ってみてください。

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

擬似twitterをつくろう

どんなものがわかりやすいかなーと考えましたが、ユーザーが多くて理解しやすいであろう、twitterのようなモノを作ってみようかと思います。ツイートするような感覚でデータベースに書き込んで、更新するとタイムラインに発言が増える、みたいな。複数のアカウントをつくることで、データベースっぽさも味わえると思います。

Accessでデータベースを作成

テーブル設計

実際の作成前に、構想を練ります。アカウント情報と、ツイート内容を格納するふたつのテーブルつくることにします。テーブルには t_ を、フィールドには f_ を頭につけた名前になっていますが、必須ではありません。（後で見たときに分かりやすいかなっていう自分ルールです。）

忘れちゃいけないのが、キー（重複しない項目）にするフィールドをつくること。アカウントはキーにできますね。（下ふたつのtwitterアカウントは架空のものです）

重複するデータを持つ場合はテーブルを分けると管理しやすくなります。例えば、t_tweetのテーブルにf_name（表示名）のフィールドを持ってしまうと、ツイートするたびに表示名も無駄に記録してしまうこととなり、変更したときには、過去の表示名を変えるのが大変になります。

別のテーブルに情報を持つことで、ツイートを読み込むときにf_id（アカウント名）を参照すればf_name（表示名）はそちらのテーブルから持ってこれるわけです。これなら、表示名が変更されても過去の全ツイートに反映できます。

では、Accessを開いてテーブルを作ってみましょう。

作成（～2003）

ご注意：こちらはこの記事を公開した当時使っていた Access2003 のキャプチャですが、さすがにもう使っていないので、2007以降のものはこの後追記してあります。該当のほうをご参照ください。

新規作成のアイコンを押すと

右側にこういうのが出てくるので、[空のデータベース]をクリック。

ファイル名を要求されます。ここで保存したファイル名と場所を後で指定するのでちゃんとメモっておいてください。

このような画面が出てきます。[デザインビューでテーブルを作成する]をダブルクリック。

さきほどの構想のとおりに、まずはt_mainのフィールド名とデータ型を指定します。説明は省略可。データ型は、数値か日付か文字列（テキスト型）か、ということを決めておきます。メモ型はテキスト型より上限文字数の大きな文字列型です。

キーは、赤く囲った部分の右クリックから設定することができます。

テーブル名は、一度保存ボタンを押すと付けることができます。

同じように、t_tweetテーブルも作成します。

ここではf_noを数値型にしていますが、オートナンバー型にしても良いと思います。レコードを追加すると自動でナンバーを振ってくれるので管理が楽です。反面、後から番号を変えることができなくなるので用途によってお好みのほうを選択してください。

これでふたつのテーブルの作成が完了しました。ここで、t_mainのテーブルにだけ、レコードを直接入力してしまいましょう。ダブルクリックで開きます。

さきほど構想したとおりに、入力します。

ご注意：本来ならばtwitterアカウントは@から文字列がはじまるのですが、Excel側のバージョンによって先頭が@だとセルに書き込みできないので、ここから先は@無しで進めていきます。ご了承ください。

ちなみにt_tweetはカラのまま。こちらへはExcelVBAから書き込んでいきますので、Accessを開いての操作はこれでおしまいです！保存して閉じてしまいましょう。

追記：作成（2007～）

2013のキャプチャなのですがおそらく2007以降はだいたい同じなんじゃないかなーと。

Accessを開いて[空のデスクトップデータベース]をクリック。

ファイル名と保存場所を指定して、ファイルを作成します。

このような画面が出るので、[表示] → [デザインビュー]をクリック。

ここでテーブル名を要求されるので作りたい名前を入力。

さきほどの構想のとおりに、まずはt_mainのフィールド名とデータ型を指定します。説明は省略可。データ型は、数値か日付か文字列（テキスト型）か、ということを決めておきます。テキスト型は2種類あって、短いテキストは255文字までという上限があります。そのぶん容量が少なくて済むので、内容次第ですね。

もうひとつテーブルを作ります。[作成]タブの[テーブルデザイン]をクリック。

t_tweetテーブルを作成します。

主キーは、該当のフィールドを右クリックで設定できます。

テーブル名は一度上書き保存するとつけられるみたいです。

これでふたつのテーブルの作成が完了しました。ここで、t_mainのテーブルにだけ、レコードを直接入力してしまいましょう。

[ホーム]タブから、[表示] → [データシートビュー]をクリック。

さきほど構想したとおりに、入力します。

t_tweetへはExcelVBAから書き込んでいきますので、カラのままでOKです。Accessを開いての操作はこれでおしまいです！保存して閉じてしまいましょう。

Excelの準備

作業シートの作成

読み書きするシートはこんな感じで！　ダサいですけどとりあえずわかりやすさ重視でw　こちらも、セルの位置を忠実に作ってもらえれば、後述のコードを手直しせずに使えると思います、幅や色や枠線なんかはお好みで大丈夫です。

ExcelVBA入門第1回　動かしてみる

こちらの記事を参考にVisualBasicEditorを開いて、Module1の作成と、3つのボタンをつくるところまでやっちゃいましょう。まだModule1には何も書いていませんので、ボタンへのプログラムの割り当ては後で行います。

VBAを省コードで書く

さて、前回Accessを連携させる基本コードを書きましたが、あれを使えば、このような形で書けると思います。（ざっくりなイメージ）

ひとつのボタンに対してひとつのプロシージャ。（Sub～End Subのまとまりをプロシージャと言います）

理解はしやすいし、これでも動くんですが…。宣言、接続、切断の全く同じコードを何度も書くというところを気持ち悪く感じてしまいます。うむむ。

そこで、このように書いてみたいと思います。「接続」と「切断」を部品化してしまいます。また、変数宣言をDimではなくPrivateで行うことで、1回の宣言でどのプロシージャでも使えるようにしておきます。よく使うのはDimですが、ほかにも宣言の仕方があるんですよー。

Dim … そのプロシージャ内のみ使える
Private … そのモジュール内のみ使える
Public … 全てのモジュールで使える

長くなりましたが、要するに省コードで実装してみますということです！

宣言・データベース接続、切断コード

Private adoCn As Object 'ADOコネクションオブジェクト
Private adoRs As Object 'ADOレコードセットオブジェクト
Private strSQL As String 'SQL文
Private Const DBpath As String = "C:\tweet.mdb" '接続するファイル（～2003）のフルパス
'Private Const DBpath As String = "C:\tweet.accdb" '接続するファイル（2007～）のフルパス

Sub DBconnect() 'DB接続プロシージャ
  Set adoCn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成
  Set adoRs = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成
  adoCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBpath & ";" 'Accessファイル（～2003）を開く
  'adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBpath & ";" 'Accessファイル（2007～）を開く
End Sub

Sub DBcut_off() 'DB切断プロシージャ
  adoRs.Close 'レコードセットのクローズ
  adoCn.Close 'コネクションのクローズ
  Set adoRs = Nothing 'オブジェクトの破棄
  Set adoCn = Nothing
End Sub

VisualBasicEditorの、先ほどつくったまっさらなModule1にこいつを貼り付けてください。貼り付けてみると区切り線が出るのでもっとわかりやすいと思います。

このコードは、先程のイメージだとこの部分にあたります。

Private宣言 … 1～5行
DB接続 … 7～12行
DB切断 … 14～19行

処理コード

さっき書いたコードの下に、各ボタンから起動するコードを書きます。

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

  '処理

  Call DBcut_off
End Sub

Sub DBwrite() '書き込み
  Call DBconnect

  '処理

  Call DBcut_off
End Sub

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

  '処理

  Call DBcut_off
End Sub

イメージだとこの部分。