 |
【Access Tips】
Accessでアプリケーションを作成する際に知っていると役立つと思われる事項を中心に掲載しています。ご自身でアプリケーションを作成される際のヒントになれば幸いです。
[テーブル編]
●プログラムファイルとデータファイルに分ける
[クエリ編]
●あいまい検索(一部一致検索)
●複数テーブルの連結参照
[フォーム編]
●条件付き書式の利用
●コマンドボタンにショトカットキーを割り当てる
●日付入力の際、/(スラッシュ)を省いて入力可能にする
●データベース起動時に特定のフォームを表示する
[レポート編]
●項目の配置
●複数列のレポート作成
●レポートをPDFファイルとして出力する方法
[マクロ編]
●AutoKeysマクロの利用法
●データベース起動時に特定の処理を実行する(AutoExec)
●マクロかVBAか
[モジュール(VBA)編]
●月末日付の求め方
●年齢の求め方
●Null値,空文字(長さ0の文字列)
●四捨五入の注意点
●AccessVBA小技集
[クライアント/サーバー編]
●JetとMSDEとSQLServer
●DAOとADO
●JetとSQLServerのSQL比較
[その他]
●Accessのバージョン
●Accessのファイル拡張子mdbとaccdb
●Accessファイル(mdb･accdb)が破損した時の対処方法
●Accessファイル(mdb･accdb)のファイルサイズが必要以上に大きくなってしまった時の対処法
●原因不明なエラーや現象発生時の対処法
●顧客管理フォームの作成
●Access関連のＱ＆Ａサイト
[テーブル編]
Accessファイル(mdb･accdb)をプログラム用ファイルとデータ用ファイルに分けているアプリケーションをよく見かけると思います。SystemWatanabeのAccessソフトもプログラムファイルとデータファイルの２つで１つのアプリケーションとして作成しています。
Accessでは１つのファイルにデータもプログラムも作成できてたいへん便利ですが、その反面、そのファイルが破損していまうと、プログラムもデータも使えなくなってしまう可能性が出てきます。
「プログラムファイルとデータファイルに分ける」とは・・・ 例えば１つのアプリケーションを作成する場合、
データ用ファイルにはテーブルのみを作成し、プログラム用ファイルにはテーブル以外のオブジェクト(フォームやレポート、モジュール、クエリなど)を作成します。プログラム用ファイルはデータ用ファイルにあるテーブルをリンクしてデータを参照します。
プログラムファイルとデータファイルを分けて作成すると下記のようなメリットが生まれます。
・実行中にファイルが破損しても大事なデータに影響を与えずに済む。
アプリケーションを実行中に何らかの異常が発生しファイルが破損してしまった場合、実行していたプログラムファイルは破損しても、リンクしていたデータファイルには影響を与えないで済む可能性が高まります。
・クライアント/サーバでデータを共有したシステムとして利用できる。
データファイルをサーバの共有フォルダへ置き、プログラムファイルを各クライアントへ置き、各クライアントのプログラムファイルからサーバのデータファイルへテーブルリンクすることにより、データのみを共有して利用することができます。(利用するデータのみがLAN上を流れるためプログラムもサーバへ置いて利用するよりも効率的で安心です)
[クエリ編]
抽出条件へ Like演算子 を使用することにより、あいまい検索を行ってデータを抽出することができます。
Like "*あ*" ･･･「あ」の文字が含まれているデータを抽出します
Like "あ*" ･･･先頭に「あ」の文字が付いているデータを抽出します
Like "[あ,い,う]*" ･･･先頭に「あ」「い」「う」のいずれかの文字付いているデータを抽出します
Like "[あ-お]*" ･･･先頭に「あ」「い」「う」「え」「お」のいずれかの文字付いているデータを抽出します
ユニオンクエリを使用して、複数のテーブルを連結して１つのテーブルのように扱うことができます。(ユニオンクエリはデザインビューからは作成できません。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]へまとめてみましたのでご参考ください。
[フォーム編]
条件付き書式を利用すると使いやすいアプリケーションが作れます。例えば・・・
入力作業中に今どこにカーソルが止まっているのか分かりづらい場合、
カーソルが止まっている項目の背景色を少し変えることにより見つけやすくなります。
方法は、フォームのデザインビューで入力項目をすべて選択し、
Accessメニューバー「書式→条件付き書式」
を選択し、「新しいルール」を選択し、「次のセルのみ書式設定」に「フォーカスのあるフィールド」を選択して背景色や文字色を指定します。
また、「次のセルのみ書式設定」に「フィールドの値」や「式」を指定することにより、ある項目の値によって項目の背景色や文字の色を変えて表示することもできます。例えば、得点が入力されている項目が50未満の場合は赤表示、70未満は黄色表示、70以上は青表示 などのように視覚的に分かりやすく表示することもできます。
項目の入力後、コマンドボタンで次の動作に移る場合など、キーボードからマウスへその都度手を動かすのは実際の作業としては不便な時があります。その場合、マウスをつかわずにキーボード操作でコマンドボタンを押下できれば入力作業が効率的になります。
コマンドボタンにショートカットキーを割り当てる方法は意外と簡単です。
例えば、「登録」ボタンをキーボードから「Alt」+「U」を押下することにより実行できるようにする場合は
コマンドボタンの標題プロパティに、 登録(&U) と指定するだけでOKです。
ボタンには [ 登録(U) ] と表示され、「Alt」+「U」を押下することにより実行できるようになります。
日付を入力する際、例えば「2013年1月5日」の場合、通常は「2013/1/5」と入力しますが、
日付項目の「定型入力」プロパティに「00/00/00」を設定すると、「130105」のように/(スラッシュ)を省いて入力することが可能となります。
ただし、それぞれ下記のようなメリット/デメリットもありますので使用用途に合わせて使う必要がありそうです。
[通常のスラッシュ付き入力]
＜メリット＞
・年を略して「1/5」のように月/日を入力すると現在の年が自動入力される
・１桁の月・日の場合「01/05」ではなく、「1/5」のように0を省いて入力可能
＜デメリット＞
・スラッシュを入力する手間がかかる
[スラッシュを省いた定型入力00/00/00]
＜メリット＞
・「130105」のように数値のみで日付が入力可能
＜デメリット＞
・年を省いて入力できない(定型入力を00/00とすれば省けるが本年以外の日付が入力できない)
・１桁の月・日の場合でも0を省けず「130105」のように必ず年2桁月2桁日2桁の計6桁入力する必要がある
データベースを起動した際に最初に表示させるフォームを設定することができます。
Accessメニューバー「ファイル→オプション→現在のデータベース→フォームの表示」
へ起動時に表示したいフォーム名を設定します。
なお、起動時のフォームを表示させずにデータベースウィンドウを表示したい場合は、Shiftキーを押下しながらデータベースを開きます。
[レポート編]
見やすいレポートを作るためには項目をきれいに並べて配置する必要があります。
項目を一つづつマウスで配置していくと僅かながらもずれあったりして、なかなかきれいに配置できなかったりします。
レポートのデザインビューで、並べて表示したい項目をすべて選択し、
Accessメニューバー「配置→配置」
を利用して簡単きれいに配置しましょう。同様に「サイズ/間隔」機能等も見やすいレポート作りにはたいへん便利です。
Accessのレポートは縦に一覧表示させる場合が多いかと思いますが、宛名ラベルや予定表など横に複数列表示させたい場合もあります。その場合は、
Accessメニューバー「ページ設定→ページ設定」
により「ページ設定」ダイアログを表示し、「レイアウト」タブ内にある「列数」へ表示させたい列数を設定します。
Accessで作成したレポートをPDFファイルにしてメール添付したり資料として保存しておきたい場合もあるかと思います。Windows10以降はプリンタ名に「Microsoft
Print to PDF」を指定して印刷するとPDFファイルとして出力できます。
(「Microsoft Print to PDF」が無いPC環境の場合でも、AdobePDFなどのPDFプリンタドライバをインストールしてプリンタ名に指定して印刷することによりPDFファイルとして出力できます)
[マクロ編]
AutoKeys(キーの自動実行)という名前のマクロを作成しておくと、そのアプリケーションを起動時に自動実行され、指定したキーに動作を割り当てることができます。
例えば、「F10」キーに「Accessを終了する」という動作を割り当てて、そのアプリケーション使用中はどのフォームからでも「F10」キーを押下することによりアプリケーションを終了させる(Accessを終了する)ことができます。
データベース起動時に特定の処理を実行させたい場合には、AutoExecという名前のマクロを作成し、そのマクロに処理を設定します。VBAで処理を記述している場合には、AutoExecのアクションへ「プロシージャの実行」を設定し、プロシージャ名を指定することでVBAモジュールも実行できます。
なお、起動時にAutoExecマクロを実行せずデータベースウィンドウを表示したい場合は、Shiftキーを押下しながらデータベースを開きます。
Accessでは動作制御などを行う場合、マクロを利用する方法とVBAを利用する方法の２通りがあります。
マクロは、それぞれ独立したコマンドを順番に実行していきます。
VBAでは、コマンドの実行、エラーの感知、条件による処理の分岐、APIの利用など柔軟な処理制御が行えます。使いやすいアプリケーションを作るためには、やはりVBAがお勧めです。
初心者の方にとってはVBAは敷居が高く感じるかと思いますが、まずは作成したマクロを「VisualBasicに変換」機能を利用してVBAに変換し少しづつVBAに触れてみるのも良いかと思います。
Accessメニューバー「デザイン→マクロをVisualBasicに変換」
[モジュール(VBA)編]
業務用アプリケーションを作成していると月末日付を求めなければならない場面によく遭遇します。
月初日付は簡単に求められます。今月の月初日付であれば
wHizuke = DateSerial(Year(Date), Month(Date),1)
これでOKです。
ところが月末日付となると、末日が30日だったり31日だったり28日だったりと固定でないので月初日付ほど簡単には求められません。ではどうすれば良いのでしょう。ポイントは、今月の月末日付は翌月の月初日付の前日だということです。これなら、月初日付の求め方とDateAdd関数を応用すれば月末日付も求められそうです。
wHizuke = DateSerial(Year(Date), Month(Date),1) ←まず今月の月初日付を求める
wHizuke = DateAdd("m", 1, wHizuke) ←今月の月初日付に１ヶ月加算し、翌月の月初日付を求める。
wHizuke = DateAdd("d",-1, wHizuke) ←翌月の月初日付から１日減算すると、今月の月末日付が求められる。
これをまとめて１行で行うと、
wHizuke = DateAdd("d", -1, DateAdd("m", 1, DateSerial(Year(Date), Month(Date),1)))
となります。
これをもっと簡単な式で求めることも出来ます。
wHizuke = DateSerial(Year(Date), Month(Date) + 1, 0)
です。DateSerial関数の構文は、 DateSerial(year, month, day) ですが、
引数monthにMonth(Date) + 1で翌月、引数dayに0を指定すると月初日付の前日、ということで翌月1日の前日となるため、当月末日付が求められます。
顧客管理などでは年齢を自動計算しなければならないこともよくあると思いますので、年齢の求め方の一例をご紹介します。
wNenrei = Year(Date) - Year(wBirymd) ←現在の西暦年から誕生日の西暦年の差を求める
If Format(Date, "mmdd") < Format(wBirymd, "mmdd")
Then ←今年の誕生日が既に来たかを判定
wNenrei = wNenrei - 1 ←今年の誕生日がまだなら１差し引く
End If
これで現在年齢が求められます。これをまとめて１行で行うと、
wNenrei = IIf(Format(Date, "mmdd") < Format(wBirymd, "mmdd"),Year(Date)
- Year(wBirymd) - 1,Year(Date) - Year(wBirymd))
となります。
なお現在、日本の法律「年齢計算ニ関スル法律」では「年齢は誕生日の前日午後12時に加算される」と定められているため、誕生日の前日のお昼12時には１つ歳を取っていることになりますが、上記の計算は一般的な解釈である「誕生日当日に年齢を加算する」方法です。
項目に値がなにも入っていない時、Null値なのか空文字(長さ0の文字列)なのかは見ただけでは判断できません。しかし、プログラム上ではNull値と空文字では扱いが異なるため注意が必要です。
If IsNull(wAtai) then
と記述すると空文字の場合にはIFを通り抜けてしまい、
If wAtai = "" then
と記述するとNull値の場合には通り抜けてしまいますので、項目に値が入っていないかを判断する場合には、
If IsNull(wAtai) or wAtai = "" then
と記述する必要があります。また、この式をまとめて、
If Nz(wAtai,"") = "" then
と記述することも出来きます。(Nz関数は第一引数のwAtaiがNull値の場合、第二引数の""(空文字)に置き換わりきますので、Null値でも空文字でもIFを通ります)
四捨五入の関数といえば、「Round関数」と思いがちですがこれには注意が必要です。
VBAでのRound関数は、"算術型" の丸め処理ではなく "銀行型" の丸め処理が行われます。
"算術型" の丸め処理とは・・・ ".5" は常に切り上げられます。
"銀行型" の丸め処理とは・・・ ".5" は、結果が偶数になるように丸め処理が行われ、切り上げられることも切り捨てられることもあります。
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
AccessVBAを利用したちょっとした小技をまとめてみましたので、下記からダウンロードしてご参考ください。
□ 単票型/データシート型のフォーム切替
□ 月初日と月末日を求める
□ 切り捨て/切り上げ/四捨五入
□ 年齢計算
□ 曜日の表示
□ メール作成ボタン
□ ホームページ表示ボタン
□ フォーム条件抽出
□ レポート条件抽出
□ 値により背景色を変える
□ ひらがなを半角カタカナに変換
□ テキストファイル出力
□ 値が入力されたらボタンを有効にする
□ 現在時刻を更新表示する
 ackowaza.zip(60KB) ※Access2003以降
[クライアント/サーバー編]
AccessのデータベースエンジンはJetデータベースエンジンと呼ばれるものです。
このJetデータベースエンジン(以下Jetと表記)を利用することによりAccessでは小規模なアプリケーションを簡単手軽に作成することができ、また、テーブルのみを保存したファイルをサーバ上の共有フォルダに置き、フォームやレポートなどを保存したファイルを各クライアントPCへ置き、サーバー上のテーブルへリンクして使用することにより簡単なクライアント/サーバシステムを作成することもできます。
Jet/MSDE/SQLServerの定義は下記のようになっています。
[Jet]
・最大255ユーザ。だたし、合計20ユーザまでを推奨。
・データ量は2GBまで
（大量のデータを扱う場合やクライアント/サーバで利用する場合にはパフォーマンスが低下する傾向がある）
[MSDE]
・5人以下のユーザが同時アクセスする場合を推奨。
・データ量は2GBまで
[SQLServer]
・同時アクセスのユーザ数は事実上制限なし。
・データ量はテラバイト単位。
DAOの正式名称は 「Data Access Objects」、ADOは「ActiveX Data Objects」です。
DAOはJet向けに開発されたインターフェースオブジェクトで、ADOはOLE DB プロバイダを介して言語に依存しないインターフェイスオブジェクトとして開発されました。
AccessからMSDEやSQLServer等を利用する場合はADOが適しているようであるため、JetからMSDEやSQLServerへ移行する際の手間を考慮して、ADOを利用することを薦める人が多いようです。
ただし、やはりDAOはJet向けに開発されたオブジェクトですので、JetにとってはDAOの方がパフォーマンス的には適してはいるようですので、このことなどを念頭に置いて、新しくAccessアプリケーションを作成する際にはDAOかADOかを選択してください。(Access2007ではDAOの新バージョンと思われるACEDAOが登場しました)
[参考情報:msdn]ADO または DAO を選択して Access データベースを使用する
Jet用のSQLとMSDEおよびSQLServer用のSQLは構文が多少異なります。
SQLについてまとめてみましたのでご参考ください。⇒[JetとSQLServerのSQL]
[その他]
Accessは現在まで Access1.1、Access95、Access97、Access2000、Access2002(XP)、Access2003、Access2007、Access2010、Access2013、Access2016、Access2019、Access2021
がリリースされてきました。
Access97以前はまだVBAが導入されていなかったため(VBAに類似した記述方法ではありましたが)Access2000以降のバージョンに移行する際モジュールをそのまま利用することができませんでした。
Access2000以降はVBAが導入され、Access2000以降のバージョン間ではあまり気にせずに移行することができるようになりましたが、新しいバージョンではプロパティが追加されたり等の変更点はあります。
会社等では複数のパソコンに異なるバージョンのAccessが存在していたりすることもあり、アプリケーションをそれぞれのパソコンのAccessバージョンに合わせて都度作成するというのもなかなか困難です。
では、異なるAccessのバージョンが存在する環境ではどのように対応すべきなのでしょう・・・
現在のAccessでは、Access2000以降のAccessファイルであれば上位バージョンのAccessでも使用できるようになっていますので、社内で使用している一番古いバージョンのAccessでアプリケーションを作成すれば、複数のバージョンが混在する環境でも同じアプリケーションを使用することができます。(上位バージョンの仕様に合わせた手直しが多少必要となる場合はあります)
Access2000/2002/2003のファイル拡張子はmdbでしたが、Access2007以降ではファイル拡張子はaccdbに変更されました。mdbファイルはAccess2007以降でも使用可能です。しかし、Access2003は2014年4月にサポート終了となりましたので、これから新たに作成するアプリケーションの場合はaccdbを使用するのが良さそうです。ただし、上位バージョンのAccessでaccdbを編集・保存すると下位バージョンのAccessで開けないという現象等が発生することがあるようですので、複数PCでAccessのバージョンが混在している環境で使用される場合は、mdbで作成しておくのが無難な場合もあります。
Accessファイルは突然破損してファイルが開けなくなったり正常に動作しなくなったりする事があります。
まずは日ごろから定期的にファイルをバックアップしておく事が大切ですが、とにかく破損したファイルを出来るだけ元の正常な状態に戻したい場合は、まず「最適化/修復」を行ってみてください。
Accessメニューバー「ファイル→データベースの最適化/修復」
それでも修復できない場合は・・・まず、新しいAccessファイル(mdbまたはaccdb)を新規作成し、
Access2010･･･メニューバー「外部データ→インポートとリンク→Access」
Access2019以降･･･メニューバー「外部データ→インポートとリンク→新しいデータソース→データベースから→Access」
から破損してしまったファイルを指定して、すべてのオブジェクトを選択しインポートを開始します。もし、完全に破損しているオブジェクトがある場合は途中でインポートが異常終了します。その場合は、その新しいファイルを再度Accesで開いてみてください。インポートが正常に完了している次のオブジェクトが破損しているオブジェクトです。次のオブジェクトを除いてその次のオブジェクトから再度インポートしてください。そうすれば、少なくとも１つのオブジェクトのみ作り直せば済みます。すべての破損ファイルに有効ではないとは思いますが、あきらめる前に試してみる価値はあると思います。
Accessファイルはテーブルの追加や削除、フォームやレポートやモジュールの修正などを行っているとファイルサイズが必要以上に大きくなったり、実行を繰り返すだけでもファイルサイズが膨張(肥大化)する場合もあり、ファイルサイズが大きくなるとパフォーマンス低下にもつながります。そのような場合、まず「最適化/修復」を行ってみてください。
Accessメニューバー「ファイル→データベースの最適化/修復」
「最適化/修復」を行ってもファイルサイズが大きすぎると思われる場合は、Accessファイルを再構築するとファイルサイズが適切に戻ることがあります。その方法は・・・まず、新しいAccessファイル(mdbまたはaccdb)を新規作成し、
Access2010･･･メニューバー「外部データ→インポートとリンク→Access」
Access2019以降･･･メニューバー「外部データ→インポートとリンク→新しいデータソース→データベースから→Access」
でファイルサイズが大きくなってしまっているAccessファイルのオブジェクトをすべてインポートします。そしてその新しいファイルでモジュールのコンパイルや起動時の設定など必要な設定を行い、そのAccessファイルのファイルサイズと以前のファイルサイズを比較してみてください。新しいAccessファイルのファイルサイズが元のファイルサイズよりかなり小さくなっているようなら、その新しいAccessファイルへ切り替えることをお勧めします。
Accessでアプリケーションを作成していると時折、まったく原因不明なエラーや現象に遭遇する場合があります。まずは、エラーメッセージに表示されているヘルプを参照してみてください。ヘルプの内容に従っても原因が判明しない場合も多々あります。そんな時は、[Microsoftサポート]サイトや[Microsoftコミュニティ]サイトの検索欄でエラーメッセージや現象などを入力して検索してみてください。同様の現象がすでに報告されていれば対処方法などが掲載されています。
MicrosoftサポートやMicrosoftコミュニティサイトにも該当事項が見当たらない場合は、Googleでエラーメッセージや現象などを入力して検索してみてください。同じトラブルに遭遇した人の解決方法等が掲載されていることも多くとても参考になります。
顧客管理フォームの作成手順を下記サイトで解説していますのでご参考ください。
第1回 [初級編]
第2回 [入力支援機能]
第3回 [売上履歴追加]
Accessでアプリケーションを作成する際など、Accessについて分からない事や疑問に思う事が発生し、ご自身の周りにもAccessに詳しい方がいない場合、
モーグ(https://www.moug.net/)
など、AccessのＱ＆Ａサイトがあります。
Ｑ＆Ａサイトを利用される際のマナーとして他の人が以前同じ質問をしていないか必ず確認してから質問しましょう。(Ｑ＆Ａサイトでは過去ログ検索機能等がありますので、まずはそこから検索してみましょう)
|
|
 
|
