今回はMicrosoftのOfficeに付いてくるデータベースソフトの「Access」で本格的な業務システムを作る場合に注意すべきポイントを紹介していきます。
はじめに
MicrosoftのAccessは、ファイル内にテーブルを作成し、リレーショナルデータベースが構築出来ます。
また、SQLを習得しなくてもクエリー機能でデータ操作が可能です。
更に、フォーム作成機能やレポート作成機能を持ち、それらは全てVBAで作り込むことが出来ます。
また、マクロ機能を使用することで、処理の自動化やボタン押下時の処理などをノンプログラミングで作ることができます。
リンクテーブル機能を使うことで、OracleやSQL Serverなどの異なるデータベースに対してODBCを介して接続ができて、あたかもそれらをローカルテーブルかの様に扱えます。
これらの機能は非常に便利であり、プログラミングに精通していなくても、Accessの基本的な機能を習得することで簡易的な業務アプリケーションが短時間に作成可能です。
また、プログラミングに精通していれば、.NETなどで開発したアプリケーションと比較しても遜色ない、本格的な業務アプリケーションが開発出来ます。
ただ、Accessが持っている機能のなかには、本格的な業務システムの構築には向かない機能も多々あります。
Accessの良いところを活用し、苦手なところは可能な限り回避する為のポイントを以下で紹介していきます。
Accessで使ってはいけない機能や注意すべきポイント
Accessで業務アプリケーションを作成するにあたって、業務アプリケーションに向かない為、使ってはいけないAccessの機能や、注意しておいたほうがよいポイントについて以下で紹介します。
リンクテーブルは可能な限り使わない
Accessの便利な機能の筆頭である「リンクテーブル」ですが、Accessで業務アプリケーションを作る場合は使わない方が良いでしょう。
理由は以下の三点です。
処理が遅い
リンクテーブルは仕組み的に、大量のデータが相手の場合、どうしても処理が遅くなります。
当ブログでも過去の記事で紹介しています。
データの抽出などの作業であれば多少の遅さも待てますが、業務アプリケーションの場合、アプリケーションのレスポンスはユーザーの使い勝手や作業効率に影響する重要なポイントです。
データベースへの接続は可能な限りADOを使用してください。
各端末でODBCの設定が必要になる
Accessでリンクテーブルを使用するには、Windowsの「ODBCアドミニストレーター」で接続先データベースを設定する必要があります。
Accessファイルで設定するリンクテーブルでは、リンク先データベースを設定した際の「DSN」の名前を管理上使用します。
例えば端末AではあるデータベースのDSNを「test」と設定し、その端末上で設定したリンクテーブルが登録されたAccessファイルを端末Bにコピーして使用する場合、端末B側でもDSNに同じ名称の「test」で設定されている必要があります。
この様に、Accessアプリケーションを使用する端末では、リンクテーブルで使用するDSNが設定されており、そのDSNの名称などの設定内容は同じである必要があります。
そのため、利用する端末が増えるほど運用、管理の手間が発生するようになります。
リンク先テーブルの定義変更が自動反映されない
リンク先のテーブルで、列の追加やデータ型変更などの定義変更があった場合、その変更は自動ではリンクテーブルへ反映されません。
定義が変わったリンク先テーブルを古いリンクテーブルでアクセスしようとするとエラーになります。
その場合はAccessのメニューから「リンクテーブルマネージャー」からリンク先の更新を実施する必要があります。
テーブル定義が変更された場合は必ずAccessアプリケーション側でも付随作業が発生することになるため手間が増えます。
マクロ機能は使わない
Accessでは「マクロ」機能があり、その機能を使うことで処理の自動化をノンプログラミングで実装できます。
予めパーツ化された各処理をGUIで設定し、一連の処理群として登録し、それを必要によって呼び出して実行します。
作成したマクロを単独で呼び出すこともできますし、それをボタン押下時のイベントとして組み込むこともできます。
プログラミングが不得手の人には便利な機能ですが、マクロで作成できる処理は、予め用意されている処理のパーツに依存するため、実装できる処理の自由度は低くなります。
そういった制約を回避して無理やり処理を作成した場合、処理が複雑になり、後から別の担当者がそのマクロをメンテナンスすることが困難になります。
また、マクロで作られた処理は解読性が低い為、マクロから別のサブマクロを呼ぶような作りだと、その後のメンテナンスも非常に大変です。
よくあるマクロの使い方だと、マクロの処理で複数の更新クエリを設定し、ボタン押下時のイベントとして組み込むといった実装を見かけますが、その場合トランザクション管理ができない為、一連の処理でエラーが発生した場合に困ることになります。
これらの理由により、マクロで処理を作るのではなく、VBAで処理を作るように実装する必要があります。
データベースへの参照や更新処理にクエリは使わない
Accessの便利な機能として「クエリ」があります。
クエリを使用すると、データベースに対してのデータ抽出や更新処理が、クエリの作成画面を使いGUIで作れます。
その為、SQLを習得しなくてもデータベースが扱えるようになる為、非技術者の人には必須の機能です。
ただ、クエリの仕組みは、GUIで作成したクエリ内容を元にAccessが裏側でSQLを自動的に作り、それを実行しているだけです。
その自動作成されたSQLはお世辞にも効率的な記述内容では無い為、複雑なクエリを作成した場合は、内部で生成されるSQLは無駄の多い処理内容になっていることが多いです。
また、クエリでデータを扱う場合、リンクテーブルを参照して処理を作るケースが多いかと思いますが、
の組み合わせは前述した過去記事の様にレスポンスが悪い為、ユーザーの操作に合せた軽快なレスポンスを必要とする業務アプリケーションとの相性は良くありません。
よって、データベースへの参照処理についてはクエリを使わずにVBAにてADOなどの機能を利用して作成する必要があります。
また、データベースへの更新関連の処理をクエリで作成する場合も問題があり、更新クエリでは前述した様に「トランザクション管理」ができません。
よって、一連の更新処理のうち、どれかでエラーが発生した場合に一連の更新結果をデータに反映することを止めて巻き戻す(ロールバック)といった実装ができない為、想定外のデータが存在した場合に、更新対象のデータの途中までは更新され、途中から更新されていないといった状態が起こり得ます。
これでは信頼性の高い業務アプリケーションは作れません。
その為、データベースへの更新処理についても、参照処理と同様にVBAのADOを使用して実装する必要があります。
Accessをデータベースとして使用しない
Accessは「データベースソフト」という分類分けになり、Accessのファイル内にテーブルをいくつも作成し、リレーショナルデータベースとして使用できます。
よってAccessをデータベースとして扱い、内部にテーブルを作り、データをドンドン貯めていくという使い方は間違ってはいないのですが、推奨できません。
もしAccessで作成する業務アプリケーションはスタンドアロンで1人の担当者しか使わず、作成されたデータを他システムで参照することが無いのであれば、Accessをデータベースとして扱いローカルテーブルでデータを管理することに問題は無いです。
ただ、Accessでは不特定多数から同時に接続を受け入れる仕組みが無い為(やろうと思えば出来ますがファイルが壊れます。)、複数人が別々のパソコンで使用する場合やそのAccessアプリケーションで作成したデータを別のシステムから参照したいといった場合には、データベースサーバ製品である「SQL Server」や「Oracle」などをデータベースとして使用する必要があります。
要するに、Accessはデータベースソフトとして使用するのではなく、業務アプリケーションの開発環境及びインターフェイスとして使用するべきということです。
では、Access内でテーブルを一つも作ってはいけないかと言うと、そういう訳ではありません。
ローカルテーブルは「ワークテーブル」として活用することができます。
.NETやJAVAなどのプログラミング言語では「Data Table」が使用できます。
プログラム内で一時的にデータを格納しておく為に使用する仮想の一時テーブルの様なものです。
データを格納するだけではなく、データの更新や追加、削除も行えます。
そのData Tableをフォーム内のデータグリッドにバインドして画面に表示したり便利な仕組みです。
VBAでは「Data Table」は使用できません。
その代り、ローカルにテーブルを作成して、それをData Tableのように使用することができます。
Accessのフォームでは、テーブルやクエリなどで取得したデータをフォームと連結する「連結フォーム」という仕組みがあり、まさに上記の「Data Tableをフォーム内のデータグリッドにバインド」と同じような実装ができます。
同じAccessファイルを同時に複数人で開かない
例えば共有フォルダにAccessのファイルを置くことで、同時に複数人でファイルを開くことができます。
Excelファイルの場合、誰かが先にそのファイルを開いていると、後から別の人が開こうとした場合にメッセージが表示され、「読み取り専用」モードで開きます。
Excelの共有設定をすることで同時に複数人で編集することもできますが、Excelの初期設定では同時に複数人での編集は許可されていません。
Accessのファイルの場合は、先にそのファイルを誰かが開いていても、Excelファイルの様なメッセージも出ないですし、複数人で開いても普段と変わりなくフォームが開き、データの登録も出来るため、一見すると同時に同じファイルを開いて使用するこは問題無さそうに感じますが、実はそのような使い方は問題があります。
例えば、同じAccessファイルをユーザーAとユーザーBが同時に開き、同じローカルテーブルの同じ行に対して、同じタイミングでユーザーAとユーザーBがそれぞれ異なる編集をするといった操作について、Accessは対応していません。
その様な使い方をした場合、Accessファイルは頻繁に壊れることになります。
よって、Accessで多人数で使用する業務アプリケーションを作る場合は、必ず端末ごとにAccessファイルを配布し、一つのAccessファイルを複数人で共有するような使い方はしないような設計をしてください。
配布したAccessファイルのバージョン管理やクライアント管理を疎かにしない
Accessで作成した業務アプリケーションは、通常Access本体がインストールされているか、AccessのRuntimeがインストールされていれば、ODBCのドライバのインストールやDSNの登録などを除けば、特別な設定をすることなくそのAccessファイルを開けばアプリケーションが使用できます。
実体はただのファイルなので、手軽にコピーしたり移動することができ、非常に便利です。
ただ、その手軽さ故に、例えばAccessファイルがコピーされて配布対象ではない端末で勝手に使用されていたり、配布対象端末の管理にミスがあり、システム管理者が把握していない端末で使用されていてそれに気付かないケースも発生します。
その場合どういった問題があるかというと、例えば作成したAccessアプリケーションで不具合が有り、それを修正したバージョンをリリースして全端末に配布したはずなのに、不具合を抱えた古いAccessアプリケーションが残っており、不具合に起因する異常データの発生が無くならないといった問題が発生します。
所謂「野良Access」状態になります。
よって、クライアント端末の数にもよりますが、Accessで業務アプリケーションを作成した場合、どの様に使用するクライアント端末を管理していくかもしっかり定義しておくべきです。
例えば、当ブログの過去の記事において、Accessで作った業務アプリケーションに自動バージョンアップ機能を実装するサンプルプログラムを紹介しています。
個人的にはこの自動更新処理を実装したことで、バージョンアップ後のファイルの配布は自動的に行われる為、バージョンアップ後のファイルをユーザーに依頼して置き換えてもらうこともなくなり、当Access業務アプリケーションを使用する端末は必ず最新の状態のファイルを使用することが担保される為、ファイル配布管理の手間が大きく軽減しました。
是非参考にして頂ければと思います。
Accessのランタイムでアプリケーションを動かす場合は要注意
AccessはMicrosoft Officeに含まれるソフトで、使用するには有償で製品やライセンスを購入する必要があります。
ただ、AccessはOffice製品群のなかでは、WordやExcelよりも専門性が高いソフトであり、製品の機能をフルで使用する人(クエリでデータ抽出処理を作成したり、業務アプリケーションを作成するなど)はそれほど多くはありません。
その為、Accessで作成した業務アプリケーションが使用だけできれば良いという端末については、Microsoftが無償で提供している「Access Runtime」というソフトをインストールすることで、Access本体がインストールされていない端末でもAccessアプリケーションが起動できるようになります。
「Runtime」は前述の通り、Accessアプリケーションの起動しかできないので、クエリを作成したりフォームを作るといった操作は一切できず、そういった機能を持っていません。
費用が掛からずにAccessアプリケーションを実行できて、ユーザーにプログラムを書き変えたりされることも無いため、システム管理者からしたら都合が良いソフトですが、実はRuntime固有の問題もあったります。
Accessで作られたアプリケーションを社内で色々と運用している場合に時々遭遇するのですが、Access本体がインストールされている端末とRuntimeだけインストールされている端末でAccessアプリケーションの挙動が異なる場合があります。
具体的にはAcces本体がインストールされている端末ではエラーにならないが、Runtimeだけインストールされている端末で動かすとエラーでアプリケーションが落ちるといったことがあります。
原因は調査して判明する場合もあれば、最終的にわからないままの場合もありますが、何となくAccess本体がインストールされている端末の方が、Runtime上で動かす場合よりVBAの記述が甘くてもエラーにならずに動くような感じがします。
またRuntimeの場合は処理でエラーが発生した場合、Access本体がインストールされている端末の様にVBAのデバッグ状態にならず、Runtimeが強制終了します。
その為、なぜ強制終了したのかわからず、慣れないと調査に時間が掛かります。
過去に、Runtimeでのエラー発生時の調査方法を記事にしているので、参考までにご一読ください。
この様にRuntime環境で業務アプリケーションを運用する前提であれば、処理ごとにしっかりとエラー処理を作り込んでおくことを推奨します。
Windows Update後にそれまで動いていたAccessアプリケーションでエラーが出る
AccessはOfficeの製品群のなかの一つであり、ExcelやWordなどのアップデートと併せてWindows Updateで更新されることは多いのですが、結構Office関連のUpdateでは不具合が混入することも多く、年に何度かWindows Updateが原因でAccessで構築した業務アプリケーションが動かなくなり、手動で特定のKBをアンインストールしたり、不具合を修正するパッチを充てたりして対応するケースが発生します。
最近あったAccessのUpdate後の不具合だと、「更新クエリを実行するとエラーになる」といった、呆れるようなケースもありました。
よって、Accessで業務アプリケーションを運用する場合は、Windows Updateをチェックし、Accessに関連する更新が無いか、社内へのWindows Update適用後は、ネット上で今回のUpdateで不具合の報告はあがってきていないかなどを気にするようにしてください。
Accessファイルが肥大化する
これも知らない人は結構いるので、是非知っておいてほしいポイントです。
一般的なデータベースでは、テーブルの行をDELETEする場合に、物理的にその対象行を削除してしまうのではなく、内部的には削除フラグを立てて、その行を非表示にしているだけです。
Accessの場合でもこれは同じであり、ローカルテーブルに対して大量の行を削除するような処理を繰り返すと、どんどんAccessファイル自体のファイルサイズが大きくなっていきます。
気が付いたらAccessファイル一つで数GB程度まで肥大化しているような状況も結構見かけます。
あまり肥大化すると、今度はAccessの動作が不安定になったり、壊れやすくなります。
Accessには「最適化」という機能があり、その最適化を実行することで、非表示扱いになっているDELETEした行を物理削除し、肥大化した状態を解消してくれます。
この「最適化」はAccessファイル内の設定で、「閉じるときに最適化」という指定ができ、それを有効にしておくことで、対象のAccessファイルを閉じる際に、毎回「最適化」処理が動き、最適化処理が完了してからAccessが終了するようになります。
肥大化するのはAccessで作成した業務アプリケーションだけではなく、データ抽出等のツールとして使用しているAccessであっても同様なので、必ず「閉じるときに最適化」の設定は有効にして運用してください。
活用したいAccessの機能やAccessで業務アプリケーションを作るメリット
Accessの機能のなかで、活用することでよりシステムの使い勝手が向上したり開発がし易くなる機能や、Accessで開発することによるメリットを紹介していきます。
Access標準のレポート機能の活用
業務アプリケーションにおいて、帳票の出力は付き物です。
納品書や明細書、何々リストなどなど。
これらを.NETなどのプログラミング言語で作成しようとした場合、昔は有償の帳票作成ソフトを購入し、そのソフトを介して出力することが一般的でした。
※最近はMicrosoft製の「Microsoftレポート」が無償で利用できるみたいです。
Accessでは、GUIでレイアウトが作れて、データベースとも連携できる強力な帳票作成機能(レポート)があります。
単票形式や表形式などが選択でき、テーブルやクエリ結果とも連携することで、データベースからデータを取得して帳票出力することも簡単に実装できます。
Accessではレポートもフォームと似た制御ができる為、レポートの開く時や閉じる時、印刷される時などにイベントが発生します。
そこにVBAで処理を記述することで、動的にラベルの内容を書き換えたり、印刷履歴データを作成するなど、色々な処理が実装出来ます。
是非活用してみてください。
ローカルテーブルをワークテーブルとして活用
ローカルテーブルを作成して、ワークテーブルとして活用することについて前述しましたが、当項でも改めて紹介します。
例えば.NETなどで作成したWindowsアプリケーションでは、Data Tableを使用して一時的なデータを保持し、画面に表示したり内部処理でデータを加工したりします。
ただ、Data Tableはあくまでそのアプリケーションが起動している間だけ使用できる一時的なテーブルの様なものなので、そのアプリケーションを終了したら、当然Data Tableに保持していたデータも消えます。
Accessのローカルテーブルをワークテーブルとして使用する場合は、そのAcessを終了しても実データとして残り続けて、明示的に削除しない限り消えません。
よって、例えばiniファイルなどの代わりに端末ごとの設定情報をローカルテーブルで管理したり、レスポンス対策の為にDBサーバ上の大きなマスタテーブルをローカルテーブルでも保持し、極力DBサーバを見に行かなくても処理が行えるようにするなどといった実装も可能です。
また、DBサーバ上のデータに対して更新を掛ける処理を実装する場合でも、例えばローカルにワークテーブルを作成し、DBサーバから更新対象のデータを取得したらそれをいったん
といった実装をすると、いったんサーバ側のデータをローカルの実データに落とし込めることで、一連の処理の実装難易度が下がって作りやすくなります。
是非参考にしてください。
ExcelやWordなどのOffice製品との連携が容易
業務アプリケーションでは、データをExcelファイルに出力したり、逆にExcelファイルを業務アプリケーション側に取り込むといった機能が必要になる場合も多いです。
その場合、通常であれば、Excelオブジェクトを生成し、それに対して細かく操作をします。
Accessの場合はExcelへのデータ出力やデータ取り込みに関するマクロ処理が用意されており、それをVBAにて呼び出すことができます。
その為、Accessで特定のテーブルのデータをExcelに出力するだけであれば、VBAでコードを1行書けば実現できます。
他にも、リンクテーブルの機能を使ってExcelファイルに対してあたかもテーブルにように接続できたりもします。
Excelなどを細かく連携しようとした場合は、Excelオブジェクトを生成してAccessからExcelを操作する従来の実装が必要になりますが、簡単な連携だけであれば、相互に連携する仕組みが多く用意されている為、それらを利用することで効率的な実装が可能です。
実はバッチ処理としても使える
担当者がAccessのクエリを使って毎月データを集計し、それをExcelに貼り付けて、表に加工するといった定例作業を行っている企業も多いかと思いますが、これらはほぼ完全な自動化が可能な業務です。
Accessではファイルを開いた際に自動で実行される処理をマクロとVBAで実装することが出来ます。
※「AutoExec」という名称のマクロを作り、そのマクロからVBAで処理が記述されたプロシージャを呼び出すなど。他にも方法はあります。
その処理のなかで、
という一連の処理を作成してあげることで、このAccessファイルを開けば以降の処理は自動化できたことになります。
更にこの「Accessファイルを開く」という作業自体は、Windowsの「タスクスケジューラー」でこのAccessファイルを開くように設定しておけば、決まった日時でこのAccessファイルを開く作業自体も自動化できます。
このタスクスケジューラーの登録を、日次実行にしておけば、毎日自動で集計してExcelに加工までしてくれるようになります。
集計作業だけではなく、データの更新やチェックなど、色々と活用は可能です。
別のプログラミング言語に精通していれば、無理にAccessを使ってバッチ処理を作成する必要は無いのですが、上記の集計作業の例の様に、人力でAccessを使って作業を自動化するといった場合は、今使用しているAccessを元に自動化処理を作っていく方が効率的です。
Accessでこのような使い方も出来ると認識しておいて頂ければ、どこかで役に立つかもしれません。
最後に
今回はAccessで業務アプリケーションを作る際のポイントを紹介させて頂きました。
Accessは不思議なもので、今回紹介したようにある程度のしっかりした業務アプリケーションの作成もできるのですが、規模の大きい業務システムの開発案件の多い大手SIerほど「習得する必要のない技術」として軽く扱われ(Windowsアプリケーションでの開発案件であれば、.NETだったりJAVAだったりで作ったほうが単価が高いですし)、企業の情シスの方が使い方を熟知しているケースの方が多いです。
ただ、企業の情シスの人はプログラマーやバリバリのIT技術者ではないケースも多いため、クエリを使ったり、簡単なフォームを作ることはできても、本格的な業務アプリケーションの作成までの技術を持っていなかったります。
個人的には、例えば自社の新規事業の立ち上げなどで、小規模なシステムを費用を掛けずに作りたい場合にAccessを用いて業務アプリケーションを内製で作成し、その後時期を見て.NETなどのWindowsアプリケーションやPHPなどのWebアプリケーションを正式にSIerが開発するといった流れが理想だと思っています。
今回も最後まで読んで頂きましてありがとうございます。
また次回もよろしくお願いいたします。
↓併せて読んでいただくと参考になるかもしれません。
