約半月前、株式会社「翔泳社」が主催した「Office Developers Conference 2003 Access Day」の1日講習に行ってきました。
場所は、茅場町のNIESセミナールーム。参加人数は、約250名程度で結構、大きな会場にほぼ満員の人でした。
私は、前から3列目ぐらいの指定席でしたので、居眠りもできず、朝9:40~夕18:30頃まで、間に昼食1時間の休憩をとっただけのかなりハードなスケジュールでした。ここでごく簡単に講習会の内容をご紹介しましょう。
セッション | 講師 | タイトル | ポイント |
1.30分 | マイクロソフト(株) 製品マーケティング本部 オフィス製品部 シニアプロダクトマネージャ 細井 智 氏 |
インフォメーションワーカーの生産性 Accessの現状と.NET時代に向けた DB活用 |
2003秋 新Office発売予定 Office XP ProfessionaにMSDE2000が付属している Access2000と2002ではパフォーマンス以外は互換 性が保たれている |
2.70分 | プロジェクトA(株)経営企画・推進室 井川 はるき 氏 |
Access VBAハイパーテクニック | テキストを取り出す(SQL文を利用) クリップボードとのやりとり ユーザーフォームの利用 プロシージャを順次実行 Accessのバージョンによる分岐処理 |
3.30分 | テクニカルライター 堀川 明 氏 | Accessによるテーブル作成のポイントと設計の 基礎 |
テーブルの設計と正規化 SQLサーバー/MSDEでは空文字(ない) とNULL(不明)を区別 |
4.70分 | テクニカルライター 長谷川 裕行 氏 | Accessで既存DB資源を有効活用するための 必須ノウハウ |
外部DBとのリンク クエリの活用 扱いやすいユーザーインターフェース |
5.70分 | (株)システムインテグレータ 代表取締役 梅田 弘之 氏 |
Accessによるネットワーク型業務アプリ、 プロジェクト活用からC/Sシステムへ |
MSDEの活用 MSDEを利用するとmdpからadp (アクセスデータプロジェクト) adpにはクライアント側にテーブルと クエリーはない(フォーム、レポート等はある) サーバー側にテーブル、ビュー、 データベースダイアグラム、 ストアードプロシージャがある ビューは、選択クエリーとほぼ 同等ではあるがフォーム上の値を検索条件 にできないのでストアードプロシージャを利用する |
6.120分 | テクニカルライター 堀川 明 氏 | アーキテクチャの基礎とMSDEデータベースの バックアップとその復旧管理 |
MDBからMSDEに乗り換えよう バックアップの取り方 DBのテーブルの物理設計(リソースの割り当て) 最初は、一つのパーテーションでもよい パフォーマンスが落ちたらインデックスのみ別領域に |
上記セミナーでも、SQL文について各所で取り上げられていました。
アクセスでは、VBAを使用しない場合、SQL文について触れることは、ほとんどないのですが、クエリーウインドウで表示メニューからSQLビューで同等のSQL文の表現を見ることができます。
SQLとは、Structured Query Language の略でデータベース照会言語とも呼ばれています。
VBAでは、DoCmdメソッドでクエリーを実行することもできますが、その内容をプログラムでコントロールしたい場合は、しはしば、同等なSQL文を直接、実行することが行われます(方法は後述)。
例えば、抽出条件付き選択クエリーと同等なSQL文は、Select A,B,C From Table Where A='1'のような形態です。
意味は、Tableから3つのフィールドを抜き出してビューを作りなさい。ただし、フィールドAの値が1のレコードのみ。
また、複数のテーブルからデータを抜き出して組み合わせる場合は、Inner Join句などで結合を行います。
更にアクションクエリーに相当するものが、Update(更新クエリー)、Insert(追加クエリー)、Select Into(テーブル作成クエリー)Delete(削除クエリー)などをSelectの代わりに使用したものです。
前項のSQL文において、私のような初学者を悩ますものとしてWhere条件の記法があります。
SQL文の全体については、クエリーウインドウで表示からSQLビューで表示されたものをコピー・貼り付けで大体、いいのですが、抽出条件については、それでは、うまくいかない場合が多いのです。
それは、Where条件を文字列式で記載しなければいけないからです。
以下では、いくつかの代表的なケースについて、その記法を取り上げてみましょう。
抽出条件 | 記 法 | 備 考 |
文字列 | "Where 文字フィールド = '文字列' " | 文字列は’でくくる |
数値 | "Where 数値フィールド = 数値 " | |
文字型変数 | "Where 文字フィールド = '" & 文字型変数 & "'" | ’” & 文字型変数 & ”’” |
日付型定数 | "Where 日付型フィールド = #日付#" | 日付は#でくくる |
日付型変数 | "Where 日付型フィールド = " & "#" & Format(日付型変数, "yyyy/mm/dd") & "#" & "" | 数値型変数の場合を参考にして |
数値型変数 | "Where 数値フィールド =" & Str(数値型変数) & "" | Str()関数は文字列に直す関数 |
特に最後の数値型変数に対応する書き方がわかりにくいのです。手元の参考書でもそれ以外の例は大体、載っているのですが、なぜか、数値型変数に対応している例が抜けています。
で、インターネット上を検索したのですが、この記法を見つけることができませんでした。
結局、あれこれ試してみて、試行錯誤で見つけたような次第です。
これらの表現は、他のケース、例えば、フィルターを使用する場合の記法にも応用できるでしょう。
うまくいかない場合は、MsgBox関数などでSQL文字列を表示してみましょう。
この際、変数を使用している場合は、変数の具体的な「値」が見えてなければなりません。
変数そのものが見えているようでは、実行時エラーが発生します。
フィールドと比較する場合、等しい場合は、等号でよいですが、大小関係、あいまいな条件の記法は、次のようになります。
比較 | 記法 |
大小 | >、<、>=、<= |
部分一致 | Like ’文字列%’ *ではなくて%を使います |
SQL文は、ADOでは、RecordsetオブジェクトのOpenメソッドで使用することが多いと思います。
これは、rs.Open mySQL,cn,カーソルタイプ,同時実行の制御,オプションという記法になります。
ここで、mySQLという文字型変数にSQL文を代入してあります。
また、rsは、レコードセット、cnは、接続を表すオブジェクトで、Dim文で宣言して、Set文で新規にオブジェクト変数を作成し、値を代入しています。
RecordsetオブジェクトのOpenメソッドは、テーブル又はクエリーの内容を参照、変更等することができます。
内容を変更する場合は、同時実行制御をadLockOptimisticにしておくとよいです。
単に参照したり、検索したり、並べ替えたりするだけであれば、adLockReadOnlyで十分です。
テーブルには、実際のデータが保存されています。データベースが使いやすく、保守しやすいデータベースになるかどうかは、大きくテーブルの設計によっています。Accessでは、容易にテーブルを作ることができるため、その設計に問題が見られるケースがあります。
例えば、次のようなテーブルからなるデータベースがありました。
これは、実際にあったものを簡素化し、フィールド名等を変えたものです。
薄黄色は、主キーの項目を表します。
また、数字は、桁数です。色の同じ項目は、同一のものを表します。
送付先データ | 会社データ | 種類データ | ||||||||
送付先ID | 長整数型 | 4 | 会社ID | 長整数型 | 4 | 種類名 | テキスト型 | 50 | ||
会社番号 | 整数型 | 2 | 会社番号 | 整数型 | 2 | 種類コードと種類名を 一つにしたもの |
||||
種類名 | テキスト型 | 50 | 会社名 | テキスト型 | 20 | |||||
送付量 | 単精度浮動小数点数型 | 4 | 種類1量 | 単精度浮動小数点数型 | 4 | |||||
送付先 | テキスト型 | 20 | 種類2量 | 同上 | 4 | |||||
どこの会社が、 何を、 どのくらい、(小数点以下2桁) どこに送ったかを記録したもの 送付先IDは、オートナンバー |
種類3量 | 同上 | 4 | |||||||
合計 | 同上 | 4 | ||||||||
送付先データに基づき集計した値を保存 会社IDは、オートナンバー 会社番号は、重複を許す |
上記には、あまり良くない点があります。改善したい点は、次のような点です。
改善したい点 |
会社データの主キーは、会社番号である 複数個、同一の会社のデータがあらわれるべきではない |
種類データは、コード部分とコード名を分離すべき |
会社データを固有部分と変動データに分離すべき |
会社/種類毎に集計データを保存するのであれば それぞれを1レコードにすべき |
合計は、計算属性であり記録すべきではない |
計算の対象としない数値型はテキスト型とすべき |
会社/種類毎の集計値は、保存しなくてもよい |
これらを考慮して、テーブルを作り直すと次のようになります。薄緑は、主キーです。
ここでは、集計値は、クエリーで一時的に作り出そうとしています。残してもよいがその場合は、参考のようにするとよいでしょう。
送付先データ | 会社マスタ | 種類マスタ | 集計データ(参考) | |||||||||||
送付先ID | 長整数型 | 4 | 会社番号 | テキスト型 | 4 | 種類コード | テキスト型 | 4 | 計上年度 | 整数型 | 2 | |||
会社番号 | テキスト型 | 4 | 会社名 | テキスト型 | 20 | 種類名 | テキスト型 | 50 | 会社番号 | テキスト型 | 4 | |||
種類コード | テキスト型 | 4 | 種類コード | テキスト型 | 4 | |||||||||
送付量 | 長整数型 | 4 | ||||||||||||
送付先 | テキスト型 | 20 | ||||||||||||
計上年度 | 整数型 | 2 | 集計値 | 長整数型 | 4 |
では、今月は、ここまで。
皆様、お元気でお過ごし下さい。また、来月、お会いしましょう。