 |
【Access講座】
この講座では、Access本などではあまり触れられていない、実際のアプリケーションを作成する際に知っていると役立つと思われる事項を中心に掲載していきたいと思います。ご自身でアプリケーションを作成される際のヒントになれば幸いです。
[テーブル編]
●プログラムMDBとデータMDBを分ける
[クエリ編]
●複数テーブルの連結参照
[フォーム編]
●条件付き書式の利用
●コマンドボタンにショトカットキーを割り当てる
●日付入力の際、/(スラッシュ)を省いて入力可能にする
[レポート編]
●項目の配置
[マクロ編]
●AutoKeysマクロの利用法
●マクロかVBAか
[モジュール編]
●月末日付の求め方
●Null値,空文字(長さ0の文字列)
●四捨五入の注意点
[CS編]
●JetとMSDEとSQLServer
●Accessプロジェクト(adp)
●DAOとADO
●JetとSQLServerのSQL比較
[その他]
●Accessのバージョン
●MDBが破損した時の対処方法
●MDBのファイルサイズが必要以上に大きくなってしまった時の対処法
●原因不明なエラーや現象発生時の対処法
●Access関連のお勧めＱ＆Ａサイト
●Access関連のお勧め書籍
[テーブル編]
プログラムMDBファイルとデータMDBファイルに分けているアプリケーションをよく見かけると思います。
私の作成したアプリケーションもほとんどのものはプログラムMDBとデータMDBを分けています。
Accessでは１つのファイル(MDB)にデータもプログラムも作成できてたいへん便利ですが、その反面、そのファイル(MDB)が破損していまうと、プログラムもデータも使えなくなってしまう可能性が出てきます。
「プログラムMDBとデータMDBを分ける」とは・・・ 例えば１つのアプリケーションを作成する場合、
データ用MDBにはテーブルのみを作成し、プログラム用MDBにはテーブル以外のオブジェクト(フォームやレポート、モジュール、クエリなど)を作成します。プログラムMDBはデータMDBにあるテーブルをリンクしてデータを参照します。
プログラムMDBとデータMDBを分けて作成すると下記のようなメリットが生まれます。
・実行中にファイルが破損しても大事なデータに影響を与えずに済む。
アプリケーションを実行中に何らかの異常が発生しMDBが破損してしまった場合、実行していたプログラムMDBは破損しても、リンクしていたデータMDBには影響を与えないで済む可能性が高まります。
・クライアント/サーバでデータを共有したシステムとして利用できる。
データMDBをサーバの共有フォルダへ置き、プログラムMDBを各クライアントへ置き、各クライアントのプログラムMDBからサーバのデータMDBへテーブルリンクすることにより、データのみを共有して利用することができます。(利用するデータのみがLAN上を流れるためプログラムもサーバへ置いて利用するよりも効率的で安心です)
ただし、MDBをクライアント/サーバで利用する場合、いろいろと制約も出てきますのでその点については下記[CS編]で解説します。
[クエリ編]
ユニオンクエリを使用して、複数のテーブルを連結して１つのテーブルのように扱うことができます。
(ユニオンクエリはデザインビューからは作成できません。SQLビューでSQL文を直接入力する必要があります)
Select 項目名1,項目名2,項目名3,･･･,項目名n
from テーブル名1
where 抽出条件式
union
all
select 項目名1,項目名2,項目名3,･･･,項目名n
from テーブル名2
where 抽出条件式
order
by 並び順
union all と指定した場合にはすべてのレコードが抽出され、union と指定した場合には重複したレコードは返されません。
JetのSQL構文については、[JetとSQLServerのSQL]へまとめてみましたのでご参考ください。
[フォーム編]
意外と知られていない「条件付き書式」。
条件付き書式を利用すると使いやすいアプリケーションが作れます。例えば・・・
入力作業中に今どこにカーソルが止まっているのか分かりづらい場合、
カーソルが止まっている項目の背景色を少し変えることにより見つけやすくなります。
方法は、フォームのデザインビューで入力項目をすべて選択し、
Access2000/Access2002/Access2003･･･メニューバー「書式→条件付き書式」
Access2007･･･リボン「デザイン→条件付き書式」
を選択し「条件」に「フォーカスのあるフィールド」を選択して背景色や文字色を指定します。
また、「条件」に「フィールドの値」や「式」を指定することにより、ある項目の値によって項目の背景色や文字の色を変えて表示することもできます。
項目の入力後、コマンドボタンで次の動作に移る場合など、キーボードからマウスへその都度手を動かすのは実際の作業としては不便な時があります。その場合、マウスをつかわずにキーボード操作でコマンドボタンを押下できれば入力作業が効率的になります。
コマンドボタンにショートカットキーを割り当てる方法は意外と簡単です。
例えば、「登録」ボタンをキーボードから「Alt」+「U」を押下することにより実行できるようにする場合・・・
コマンドボタンの標題プロパティに、 登録(&U) と指定するだけでOKです。
ボタンには [ 登録(U) ] と表示され、「Alt」+「U」を押下することにより実行できるようになります。
日付を入力する際、例えば「2008年1月5日」の場合、通常は「2008/1/5」と入力しますが、
日付項目の「定型入力」プロパティに「00/00/00」を設定すると、「080105」のように/(スラッシュ)を省いて入力することが可能となります。
ただし、それぞれ下記のようなメリット/デメリットもありますので使用用途に合わせて使う必要がありそうです。
[通常のスラッシュ付き入力]
＜メリット＞
・年を略して「1/5」と入力すると現在の年が自動入力される
・１桁の月・日の場合「01/05」ではなく、「1/5」として0を省いて入力可能
＜デメリット＞
・スラッシュを入力する手間がかかる
[スラッシュを省いた定型入力00/00/00]
＜メリット＞
・数値のみで日付が入力可能
＜デメリット＞
・年を省いて入力できない(定型入力を00/00とすれば省けるが本年以外の日付が入力できない)
・１桁の月・日の場合でも0を省けず「080105」のように必ず年2桁月2桁日2桁の計6桁入力する必要がある
[レポート編]
見やすいレポートを作るためには項目をきれいに並べて配置する必要があります。
項目を一つづつマウスで配置していくと僅かながらもずれあったりして、なかなかきれいに配置できなかったりします。
レポートのデザインビューで、並べて表示したい項目をすべて選択し、
Access2000/Access2002/Access2003･･･メニューバー「書式→配置」
Access2007･･･リボン「配置→コントロールの配置」
を利用して簡単きれいに配置しましょう。同様に「サイズの変更」や「左右の間隔」、「上下の間隔」機能等も見やすいレポート作りにはたいへん便利です。
[マクロ編]
AutoKeys(キーの自動実行)という名前のマクロを作成しておくと、そのアプリケーションを起動時に自動実行され、指定したキーに動作を割り当てることができます。例えば・・・
「F10」キーに「Accessを終了する」という動作を割り当てて、そのアプリケーション使用中はどのフォームからでも「F10」キーを押下することによりアプリケーションを終了させる(Accessを終了する)ことができます。方法は、「AutoKeys」という名前のマクロを新規作成し、
マクロ名：{F10}
アクション：終了
と指定してマクロを保存します。これで、そのアプリケーションは「F10」を押下することにより終了することができるようになります。
Accessでは動作制御などを行う場合、マクロを利用する方法とVBAを利用する方法の２通りがあります。
マクロは、それぞれ独立したコマンドを順番に実行していきます。
VBAでは、コマンドの実行、エラーの感知、条件による処理の分岐、APIの利用など柔軟な処理制御が行えます。使いやすいアプリケーションを作るためには、やはりVBAがお勧めです。
初心者の方にとってはVBAは敷居が高く感じるかと思いますが、まずは作成したマクロを「VisualBasicに変換」機能を利用してVBAに変換し少しづつVBAに触れられていってはいかがでしょう。
Access2000/Access2002/Access2003･･･メニューバー「ツール → マクロ→マクロをVisualBasicに変換」
Access2007･･･リボン「データベースツール→マクロをVisualBasicに変換」
[モジュール編]
業務用アプリケーションを作成していると月末日付を求めなければならない場面によく遭遇します。
月初日付は簡単に求められます。今月の月初日付であれば
wHiduke = DateSerial(Year(Date), Month(Date),1)
これでOKです。
ところが月末日付となると、末日が30日だったり31日だったり28日だったりと固定でないので月初日付ほど簡単には求められません。ではどうすれば良いのでしょう。ポイントは、今月の月末日付は翌月の月初日付の前日だということです。これなら、月初日付の求め方とDateAdd関数を応用すれば月末日付も求められそうです。
wHiduke = DateSerial(Year(Date), Month(Date),1) ←まず今月の月初日付を求める
wHiduke = DateAdd("m", 1, wHiduke) ←今月の月初日付に１ヶ月加算し、翌月の月初日付を求める。
wHiduke = DateAdd("d",-1, wHiduke) ←翌月の月初日付から１日減算すると、今月の月末日付が求められる。
これを、まとめて１行で行うと、
wHiduke = DateAdd("d", -1, DateAdd("m", 1, DateSerial(Year(Date),
Month(Date),1)))
となります。
月末日付を求める上で特に注意しなければならいのは、月末日付から前月の月末日付を求めたり、翌月の月末日付を求めたりする場合です。wHidukeには今月の月末日付が入っていることにして、翌月の月末日付を求めるようとする場合、
wHiduke = DateAdd("m", 1,wHiduke) で簡単に求められそうですが、
もし、今月の月末日付が２月２８日とすると、３月２８日が翌月の月末日付という結果となってしまいます。これでは、まずいですよね。この場合の考え方は、
月末日付に１日を加算すれば翌月の月初日付になる。その月初日付に１ヶ月加算し、そこから１日減算すれば翌月の月末日付となります。式で表すと、
wHiduke = DateAdd("d", -1, DateAdd("m", 1,DateAdd("d", 1, wHiduke)))
となります。前月の月末日付の場合は、
wHiduke = DateAdd("d", -1, DateAdd("m", -1,DateAdd("d", 1, wHiduke)))
です。なんだか簡単そうでちょっと複雑ですが、日付の計算には注意しましょう。
項目に値がなにも入っていない時、Null値なのか空文字(長さ0の文字列)なのかは見ただけでは判断できません。しかし、プログラム上ではNull値と空文字では扱いが異なるため、
If IsNull(wAtai) then
と記述すると空文字の場合にはIFを通り抜けてしまい、
If wAtai = "" then
と記述するとNull値の場合には通り抜けてしまいます、結局のところ
If IsNull(wAtai) or wAtai = "" then
と、記述しなければいけません。また、こんな記述も出来きます。
If Nz(wAtai,"") = "" then
wAtaiがNull値の場合、空文字に置き換わりきますので、Null値でも空文字でもIFを通ります。
四捨五入の関数といえば、「Round関数」と思いがちですがこれには注意が必要です。
VBAでのRound関数は、"算術型" の丸め処理ではなく "銀行型" の丸め処理が行われます。
"算術型" の丸め処理とは・・・ ".5" は常に切り上げられます。
"銀行型" の丸め処理とは・・・ ".5" は、結果が偶数になるように丸め処理が行われ、切り上げられることも切り捨てられることもあります。(参考情報)Microsoftサポート技術情報⇒VBAのRound関数について
Excelのワークシート関数のRoundは算術型丸めを行っていますが、VBAのRound関数も算術型丸めだと思って使ってしまうと、思わぬ結果をもたらしてしまいます。
四捨五入・切り捨て・切り上げの計算は下記のように行えます。
[小数以下第１位を四捨五入する場合]
If wAtai > 0 Then
wAtai = Fix(wAtai + 0.5)
Else
wAtai = Fix(wAtai - 0.5)
End If
[小数以下第１位を切り捨てる場合]
wAtai = Fix(wAtai)
[小数以下第１位を切り上げる場合]
If wAtai > 0 Then
wAtai = Fix(wAtai + 0.9)
Else
wAtai = Fix(wAtai - 0.9)
End If
[CS編]
AccessのデータベースエンジンはJetデータベースエンジンと呼ばれるものです。
このJetデータベースエンジン(以下Jetと表記)を利用することによりAccessでは小規模なアプリケーションを簡単手軽に作成することができます。しかし、Jetは大量のデータを扱う場合やクライアント/サーバで利用する場合にはパフォーマンスが低下します。その問題を補うために、Acces2000からAccessプロジェクト(adp)という方法を利用してデータベースエンジンにMSDEやSQLServerを利用することが可能となりました。
大量なデータを扱う場合や本格的なクライアント/サーバ向けシステムをAccessで構築する場合はこのAccessプロジェクトを利用してデータベースエンジンにMSDEまたはSQLServerを利用することが望ましいようです。
Jet/MSDE/SQLServerの定義は下記のようになっています。
[Jet]
・最大255ユーザ。だたし、合計20ユーザまでを推奨。
・データ量は2GBまで
（大量のデータを扱う場合やクライアント/サーバで利用する場合にはパフォーマンスが低下する傾向がある）
[MSDE]
・5人以下のユーザが同時アクセスする場合を推奨。
・データ量は2GBまで
[SQLServer]
・同時アクセスのユーザ数は事実上制限なし。
・データ量はテラバイト単位。
Accessプロジェクトについては右記サイトをご参考ください。⇒Microsoft Office アシスタント：Accessプロジェクトについて
通常、Accessでアプリケーションを作成する場合は拡張子が「.mdb」のファイルとなりますが、Accessプロジェクトでは拡張子が「.adp」のファイルとなります。
また、通常のAccessデータベースではJetをモジュール(VBA)からデータ操作する場合DAO(通称:ダオ)と呼ばれるオブジェクトを利用していましたが、AccessプロジェクトではADO(通称：エーディーオー)と呼ばれるオブジェクトを利用する必要があります。
DAOの正式名称は 「Data Access Objects」、ADOは「ActiveX Data Objects」です。
DAOはJet向けに開発されたインターフェースオブジェクトで、ADOはOLE DB プロバイダを介して言語に依存しないインターフェイスオブジェクトとして開発されました。
AccessプロジェクトでMSDEまたはSQLServerを利用する場合は必ずADOを利用する必要がありますが、AccessデータベースでJetを利用する場合はDAO,ADOのどちらでも利用可能です。MicrosoftでのDAOの更新は既に終了しているようですし、JetからMSDEやSQLServerへ移行する際の手間を考慮して、今後Accessアプリケーションを作成する際にはADOを利用することを薦める方が多いようです。
ただし、やはりDAOはJet向けに開発されたオブジェクトですので、JetにとってはDAOの方がパフォーマンス的には適してはいるようです。このことを念頭に置いて、新しくAccessアプリケーションを作成する際にはDAOかADOかを選択してください。
[参考情報:msdn]ADO または DAO を選択して Access データベースを使用する
Jet用のSQLとMSDEおよびSQLServer用のSQLは構文が多少異なります。
SQLについてまとめてみましたのでご参考ください。⇒[JetとSQLServerのSQL]
[その他]
Accessは現在まで、Access1.1⇒Access95⇒Access97⇒Access2000⇒Access2002(XP)⇒Access2003⇒Access2007
とリリースされてきました。
Accessアプリケーションを作成・利用する上で心配される事のひとつに、バージョンが変わると以前作成したアプリケーションが利用できなくなる可能性があるという事が挙げられます。特にAccess97以前はまだVBAが導入されていなかったため(VBAに類似した記述方法ではありましたが)Access2000以降のバージョンに移行するとモジュールをそのまま利用することができません。Accessにはバージョン移行を考慮して自動変換機能が搭載されており、とりあえずその機能を利用して自動的に変換することができますが、自動変換機能も完璧ではありませんので変換後、構文チェックや動作チェックを行い修正する必要があります。
Access2000以降はVBAが導入され、現在のところAccess2000以降のバージョン間ではあまり気にせずに移行することができるようになりました。
しかし、会社等では複数のパソコンに異なるバージョンのAccessが存在していたり、個人の場合でもデスクトップパソコンとノートパソコンに異なるバージョンのAccessを利用していたりすることもあり、アプリケーションをそれぞれのパソコンのAccessバージョンに合わせて都度変換するというのもなかなか困難です。
では、異なるAccessのバージョンが存在する環境ではどのように対応すべきなのでしょう・・・
Access2000以降のバージョンAccess2002やAccess2003ではAccess2000ファイル形式がサポートされており、Access2000ファイル形式で作成したアプリケーションを変換することなくそのまま利用することが可能です。ですから現時点では、Access2000以降のバージョンが混在する環境であればAccess2000ファイル形式でアプリケーションを作成して利用することが最適ではないかと私としては考えています。
Access2007もAccess2000ファイル形式がサポートされていますので、基本的にはAccess2000ファイル形式で作成したアプリケーションを利用できますが、以前とは仕様が異なる箇所が幾つかあるようなので多少手直しが必要な場合がありますが、VBA自体は変わっていませんのでAccess2007の仕様に合わせて手直しすればAccess2000〜Access2007で動作可能なAccess2000ファイル形式で運用可能と思われます。
Accessファイルは突然破損してファイルが開けなくなったり正常に動作しなくなったりする事があります。
まずは日ごろから定期的にファイルをバックアップしておく事が大切ですが、とにかく破損したファイルを出来るだけ元の正常な状態に戻したい場合は、まず「最適化/修復」を行ってみてください。
Access2000/Access2002/Access2003･･･メニューバー「ツール→データベースユーティリティ→最適化/修復」
Access2007･･･Officeボタン「管理→データベースの最適化/修復」
それでも修復できない場合は・・・まず、新しいMDBを新規作成し、
Access2000/Access2002/Access2003･･･メニューバー「ファイル→外部データの取り込み→インポート」
Access2007･･･リボン「外部データ→インポート→Access」
で破損してしまったMDBファイルを指定して、すべてのオブジェクトを選択しインポートを開始します。すると、おそらく途中でインポートが異常終了します。そしてその新しいMDBを再度Accesで開いてみてください。インポートが正常に完了している次のオブジェクトが破損しているオブジェクトです。次のオブジェクトを除いてその次のオブジェクトから再度インポートしてください。そうすれば、少なくとも１つのオブジェクトのみ作り直せば済みます。すべての破損ファイルに有効ではないとは思いますが、あきらめる前に試してみる価値はあると思います。
また、Microsoftサイト[破損した Jet 4.0 データベースの Access 2000 でのトラブルシューティングと修復の方法]にも破損したファイルの修復方法や破損原因等が掲載されていますのでご参考ください。
Accessファイルはテーブルの追加や削除、フォームやレポートやモジュールの修正などを行っているとファイルサイズが必要以上に大きくなりパフォーマンス低下につながります。そのような場合、まず「最適化/修復」を行ってみてください。
Access2000/Access2002/Access2003･･･メニューバー「ツール→データベースユーティリティ→最適化/修復」
Access2007･･･Officeボタン「管理→データベースの最適化/修復」
「最適化/修復」を行ってもファイルサイズが大きすぎると思われる場合は、ＭＤＢを再構築するとファイルサイズが適切に戻ることがあります。その方法は・・・まず、新しいＭＤＢを新規作成し、
Access2000/Access2002/Access2003･･･メニューバー「ファイル→外部データの取り込み→インポート」
Access2007･･･リボン「外部データ→インポート→Access」
でファイルサイズが大きくなってしまっているＭＤＢのオブジェクトをすべてインポートします。そしてその新しいＭＤＢでモジュールのコンパイルや起動時の設定など必要な設定を行い、そのＭＤＢのファイルサイズと以前のファイルサイズを比較してみてください。新しいＭＤＢのファイルサイズが元のファイルサイズよりかなり小さくなっているようなら、その新しいＭＤＢへ切り替えることをお勧めします。原因は詳しくは分かりませんが、ＭＤＢに何度も追加・修正・削除を行っているとファイルサイズが最適化では直らず巨大化してくる場合があります。
Accessでアプリケーションを作成していると時折、まったく原因不明なエラーや現象に遭遇する場合があります。まずは、エラーメッセージに表示されているヘルプを参照してみてください。ヘルプの内容に従っても原因が判明しない場合も多々あります。そんな時は、[Microsoftサポート技術情報]サイトの検索欄でエラーメッセージや現象などを入力して検索してみてください。同様の現象がすでに報告されていれば対処方法などが掲載されています。
また、Access自身の不具合による現象である場合も多々ありますので、必ず、Accessを最新の状態に更新することをお勧めいたします。Accessの更新(Update)は[Microsoft Office Update]から行えます。
Microsoftサポート技術情報にも該当事項が見当たらず、OfficeUpdateを行っても解決しない場合は、Google検索エンジン等にエラーメッセージや現象などを入力して検索してみてください。同じトラブルに遭遇した人の解決方法等が掲載されていたり、とても参考になります。
Accessでアプリケーションを作成する際など、Accessについて分からない事や疑問に思う事が発生し、ご自身の周りにもAccessに詳しい方がいない場合などは、AccessのＱ＆Ａ掲示板や会議室サイトを利用されてはいかがでしょうか。
Access初心者の方には・・・Access Club (http://www.accessclub.jp/)のAccess初級者対象Forumがお勧めです。
MSDEやSQLServerに関連した内容も知りたい方は・・・RDBMSの電子会議室 pin's Laboratory (http://www7.big.or.jp/~pinball/)がお勧めです。
Ｑ＆Ａ掲示板や会議室サイトを利用される際のマナーとして他の人が以前同じ質問をしていないか必ず確認してから質問しましょう。(Q&A掲示板や会議室サイトでは過去ログ検索機能等がありますので、まずはそこから検索してみましょう)
|
|
|
