オフィスのパソコンは年々増え続ける。
それに伴い従来のAccessアプリケーションはだんだんと遅くなるばかり・・・。
何か良い方法はないものか・・・。
はい、あります！それがAccessADP！
AccessADPはAccess自身のデータベースエンジンを利用せずにワンランク上のSQL server などを利用する真のクライアント＆サーバーシステムです。
クライアント台数が10台以上になったらSQL server + AccessADP！
ここでご紹介するドキュメントはAccessMDBからAccessADPへ移行しようとする皆さんに最適な情報を提供するためのものです。ダウンロードファイルには分かりやすいADPサンプルプログラムを同梱。ドキュメントではサンプルプログラムを丁寧に解説しています。もちろん、データベース作成のためのスクリプトファイルも添付。AccessとSQL Server 2005（または無料配布されているExpress版）さえあればすぐにサンプルプログラムが動作します。
※このドキュメントはSQL Server入門ドキュメントとしても活用できます。
※ビットのADPノウハウをこっそりお教えします！
 見て、触って、簡単にADPが学習できます。有料ダウンロードはこちらから。
「ＡｃｃｅｓｓMDBからＡｃｃｅｓｓADPへ」移行支援ドキュメント（無料版）はこちらから。
※無料版では有料版の約半分近くのページを公開しています！
【目次項目】
○AccessMDBからAccessADPへ
○データベース作成から空のADPファイル作成までの手順
○SQL Serverのストアドプロシージャとは
○ストアドプロシージャの作成方法
○簡単なメイン＆サブフォームの作り方
○ADPで検索フォームを作る
○ＭＤＢでできてＡＤＰで出来ないこと？
○ＳＱＬの実行結果の違い
○ＡＤＰ起動後に接続データベースを切り替える方法
○ストアドプロシージャの抽出条件を動的に変更するには
○テーブルコンバートの注意点
○一時テーブル（作業用テーブル）や関数の利用方法について
○SQL Serverデータのバックアップとリカバリー方法
○SQL Serverトランザクションログファイルの圧縮方法
○Express版での定期バックアップ
○付属アプリケーションを使用するための準備
総ページ数97P（１P48行）
※青字は無料版で公開されている項目です。
 サンプルプログラムメインメニュー
●『AccessMDBからAccessADPへ』より抜粋
AccessADPが世に出たのはAccess2000バージョンとなってからですが、残念ながらAccessADPは現在に至るまでそれほど多くは普及しませんでした。その原因はおそらく次のようなものではなかろうかと考えます。
【AccessADPがそれほど普及しなかった原因】
・AccessMDBでも処理速度が十分速かった
・10台程度までのクライアント数ならAccessMDBでも問題はなかった
・連携させるためのデータベースシステムが高価だった
・連携させるためのデータベースシステムを新たに習得しなければならなかった
・AccessADP方式の情報が乏しかったから
|
正確な原因は一ユーザーである私たちには分かりませんが、ただ一つだけはっきりしていることがあります。
それは、
|
AccessADP（AccessADP＋SQL Server）は「VB + SQL Server」あるいは「.NET + SQL Server」よりもコストパフォーマンスにおいて断然優れている！！
|
ということ（※ただし、販売管理や生産管理などの基幹業務系において）。
中でも、AccessADPの「プログラミングの容易さ」や「メンテナンス性の高さ」においては他を圧倒しています。一般的に言ってもVBや.NETはAccessに比べてかなりのプログラミングスキルが必要とされますが、AccessADPならこれまで慣れ親しんできたAccessMDBでの知識がほぼそのまま活用できます。このことは利用者にとってどんなに心強いものとなるか容易に想像できます。
【AccessADPがVBや.NETより優れている点】
・これまでのAccessVBAがほぼそのまま利用できること（一部書き換えが必要）
・レポート作成においては群を抜いて簡単であること
・SQL Serverとの親和性が高いこと（AccessADP側からテーブルやストアドプロシージャ作成が可能）
|
|
●『簡単なメイン＆サブフォームの作り方』より抜粋
MDBで入力フォームを作成するとき、多くはメインフォームのレコードソースからクエリビルダを起動して、以下のようなクエリを作成することでしょう。
【AccessMDBでよく使われたレコードソース】
select * from 売上入力メイン
|
ところが上記のADP入力フォームでは以下のようなストアドプロシージャを指定しています。
【今回のAccessADP売上処理メインフォームで使われるレコードソース】
CREATE PROCEDURE usp_売上入力メイン
(
@伝票番号 int
)
AS
select * from 売上入力メイン
where 伝票番号=isnull(@伝票番号,0)
RETURN
|
※ストアドプロシージャの基本的な事柄については『ストアドプロシージャとは』の項を参照してください。
両者を比較すると、『AccessMDBではWhere句を用いていなかったがAccessADPでは用いている』ということが分かります。そして、その違いがなぜなあるのかがAccessADP開発を行う上での重要なポイントとなるのです。
・・・途中省略・・・
|
Me.InputParameters = "@伝票番号 int=" & lng伝票番号
|
一見すると上のステートメントはフォームのインプットパラメータに値を代入しているだけのように思われますが、実はこれによってフォームに新しいデータが表示されることとなります。言わば、MDBでのReqeryと同じ働きを行ってくれることになるわけです。
|
●『ＭＤＢでできてＡＤＰで出来ないこと？』より抜粋
ストアドプロシージャではMDBのクエリ内に記述していた「=[fomrs]![メインフォーム名]![オブジェクト名]」という抽出条件式は利用できません。その理由は、SQL ServerからはダイレクトにAccessのコントロールを参照することが出来できないからです。
・・・途中省略・・・
・クロス集計がない？
下図はMDBにおけるクロス集計結果の一例です。このクエリでは指定した期間内で、得意先ごと月別の売上を算出しています。
MDBではこのような結果を求めるために次のようなクエリを作成します。
・・・途中省略・・・
表示されたSQLをすべてコピーし、今度はADP形式のストアドプロシージャ内に貼り付けて利用してみましょう。新しく作成するストアドプロシージャ画面で、SQL文を表示させ、そこにコピーしたSQL文を上書き貼り付けすると以下のような画面が表示されます。
・・・エラーメッセージ表示画面
これは、AccessMDBのクロス集計クエリで使用していた、「TRANSFORM」や「PIVOT」等という句はSQL Serverでは使用できない、というエラーメッセージです。
ではどのようにしてクロス集計クエリを実現するのでしょうか？
サンプルプログラム内の「年間売上集計」を例にとって解説していきましょう。
・・・途中省略・・・
同様に、他の月の合計を算出するためのSQL文を書き足して完成した「年間売上集計」のストアドプロシージャの内容は以下のようになります。
※項目名の先頭に数字があるものは［］で囲まないとエラーになります。
ALTER PROCEDURE usp_年間売上集計
(
@得意先C1 int, @得意先C2 int,
@期間1 datetime, @期間2 datetime
)
AS
select 売上台帳メイン.得意先C, 得意先マスタ.得意先名,
sum(case when month(売上台帳メイン.売上日)=1 then 売上台帳明細.金額 else 0 end) as [1月],
・
途中省略
・
sum(case when month(売上台帳メイン.売上日)=12 then 売上台帳明細.金額 else 0 end) as [12月],
sum(売上台帳明細.金額) as 合計金額,
得意先マスタ.短縮名称
from 売上台帳メイン
inner join 売上台帳明細 on 売上台帳メイン.伝票番号 = 売上台帳明細.伝票番号
inner join 得意先マスタ on 売上台帳メイン.得意先C = 得意先マスタ.得意先C
where 売上台帳メイン.売上日 between @期間1 and @期間2
and 売上台帳メイン.得意先C between @得意先C1 and @得意先C2
group by 売上台帳メイン.得意先C, 得意先マスタ.得意先名, 得意先マスタ.短縮名称
order by 売上台帳メイン.得意先C
RETURN
|
|
●『ＳＱＬの実行結果の違い』より抜粋
MDB形式のUPDATE文とADP形式のUPDATE文では実行した結果が異なる場合があります。
たとえば、次のような二つのテーブルがあり、テーブルＡの商品ごとの［数量］の合計をテーブルＢの［数量合計］にセットするクエリを考えます。
［テーブルＡ］
 |
［テーブルＢ］
 |
 |
同じSQLで［数量合計］
を算出する。 |
|
［MDBでの実行結果］
 |
［ADPでの実行結果］
 |
［MDBでの実行結果］では商品ごとの正しい合計値が［数量合計］にセットされているのに対し、［ADPでの実行結果］では、同じようなクエリ（この場合はストアドプロシージャ）を作成して実行すると違った値がセットされてしまいました。
MDB形式の場合では以下のクエリを実行します。
①［数量合計］の値をクリアする
・・・途中省略・・・
②［数量合計］に［数量］の値を加算する
↓（クエリを実行する）
［数量合計］にテーブルＡの合計値が算出されました。
ちなみに、上図［数量合計をセットする］クエリのSQL文はこうなります。
|
UPDATE B INNER JOIN A ON B.商品 = A.商品 SET B.数量合計 = Nz([B]![数量合計],0)+Nz([A]![数量],0);
|
それでは、ADP形式の場合はどうなるのでしょうか？
ストアドプロシージ内で使用するＴ－ＳＱＬとＡｃｃｅｓｓMDBでは構文が多少異なりますので注意して作成します。
ここでは次のようなストアドプロシージャを作成しました。
ALTER PROCEDURE 数量合計をセットする_MDB
AS
SET NOCOUNT ON
-- 数量合計をクリアする
update B set 数量合計 = 0
-- 数量合計をセットする
update B set
数量合計 = isnull(B.数量合計, 0) + isnull(A.数量, 0)
from B inner join A on B.商品 = A.商品
RETURN
|
これを実行すると、先に述べたように期待しない結果になってしまうのです。
・・・途中省略・・・
ちなみに、「数値合計をセットする」部分のSQL文を
update B set 数量合計 = sum(isnull(A.数量, 0))
from B inner join on B.商品 = A.商品
group by B.商品
|
としたくなるところですが、UPDATE文のSET句に集計関数は使えないため、エラーになります。
|
●『定期的にバックアップを行うには』より抜粋
定期的にバックアップを行うにはSQL Server エージェント（以下、エージェント）を利用します。
SQL Serverエージェントは無料配布されているExpress版を除くすべてのSQL Serverに備わっていて、例えば「○時にデータの一括変換をやりたい」とか「△時にレジ売上のテキストデータの取り込みをしたい」といった作業を行いたいときに利用します。
これらの作業をジョブとして登録し、任意の時間に実行させることができます。
毎日定時にバックアップを取りたい場合にもこのエージェントを利用します。ただし、前述のようにSQL Serverを構成しているファイルにはデータファイルとログファイルという２種類の重要なファイルがありますのでこの両方をバックアップする必要があります。また、バックアップ処理後にデータベースの圧縮を行う必要もあります。
SQL Serverの圧縮とはAccessでの圧縮とほぼ同様と解釈すればよいでしょう。圧縮を行うことでデータベースとトランザクションログファイルのファイルサイズを小さくすることができます。特に、ログファイルはそのまま放置しているとデータベースファイルサイズ以上に容量が肥大化しますので注意が必要です。このため、先に述べた「データベースのバックアップ」というジョブ作成時には、最低でも以下の処理をセットで行う必要があります。
① データベースのバックアップ
② トランザクションログのバックアップ
③ データベースの圧縮
・・・以下省略・・・
★本ドキュメントではExpress版での定期バックアップ方法についても解説しています。
※ダウンロードファイルには以下のファイルが同梱されています。
○AccessMDBからAccessADPへ.doc（ドキュメント）
○prg.adp（サンプルプログラム）★このプログラムはAccess2003とAccess2007で動作します。
○bitadp.sql（データベース作成のためのスクリプトファイル）
○readme.txt
※サンプルプログラムを動作させるためにはマイクロソフト社のSQL Server 2005またはSQL Server 2005 Express Edition (無料)が必要です。
※ADPサンプルプログラムはAccess2003、Access2007に対応しています。
【ビットから一言】
やっぱりSQL Serverはスゴイ。
クライアントが10台以上になったら迷わずSQL Server＋AccessADPです！
|
|
|
