MS Access

Accessであらかじめリンクテーブルを開いておくと劇的に高速化

投稿日:2014年12月11日 更新日:

リンクテーブルに対して「Dlookupで値を探して見つかったらCurrentDb.Execute UPDATE~ で値を更新」という処理をループで回している。

この処理、一旦始めると、終えるのに30分以上かかることがある(件数は100件未満なのに)。
使っている人に、何とかして欲しい・・・と言われた。

調べてみると、コード中で単純なDlookupをしている1行だけでも、3秒ほど結果が出るのを待たされていることに気がついた。(イミディエイトウィンドウで確認)
数万件のテーブルとはいえ、参照しているだけなのにこんな時間がかかるはずがない。変だ。
で、試しにこのDlookupで参照しているテーブルをあらかじめ開いておいて、もう一度同じ処理を回してみた。

すると、100倍くらい速くなった! 数秒で終わる。

テーブルそのものでなく、そのテーブルを使っているフォームをhiddenで開いておいて、(閉じないで)そのあと見えなくしたものでも同じくらいに速くなった。

DoCmd.OpenForm “master”, , , , , acHidden
Forms!master.Visible = False

テーブルやフォームを開かず、処理の直前でレコードセットを作っても、同じくらいに速くなった(作ったrsは使わない)。

Set DB = CurrentDb
Set rs = DB.OpenRecordset(“master”)

ということは、ひょっとすると、DlookupとCurrentDb.Execute SQL といった関数やSQL文の組み合わせを使わず、rsのレコードセットを使い、素直にFindしたりUpdateするのが良いということかもしれない。(けれどそれだとコード量が増えて、見通しが悪くなるしナァ・・・)

また、Access2013よりも、Access2000と2007の方が、パフォーマンスが劇的に変わった。
こうしておくと、テーブルの内容をメモリに読み込んで処理するのかな?

これは発見!
しかしGoogleで検索しても、同じようなことを書いている人は見つけられなかった。
みんなが知らないことなの?
それとも、自分の環境だけに依存する現象?

よく分からないけど、結果オーライ。

-MS Access

執筆者:

関連記事

no image

初めてのSQL Server 2008 R2 Express Editionでつまづいたことの記録

やりたかったことは、 1.Accessのとあるテーブルを、SQL Server 2008にインポートして、複製を作る。 2.上記で作ったテーブルを、AccessからODBCで読め、かつパススルークエリ …

no image

Accessでレコードコピー(オートナンバー除く)の決定版のコード!

Accessで、レコードのオートナンバーを除くフィールドを1行、複製(コピー)したくって、Google検索したのだけどなかなか出てこず、しかしやっと見つけた、黄金のソース・・・・。 [VBA] ADO …

no image

「3070 有効なフィールド名または式として認識できません」エラーで、困った

Accessで、クエリ単体での実行だとうまくいくのに、いざ、そのクエリをVBAコード内で実行すると、上記エラーが出る。 難問だった。 SQL文内でDISTINCTを使っているからかなーとか、LEFT …

no image

Excelで使える祝日判定のWebAPIを見つけた

Excelの関数には、曜日を判定できる関数(WEEKDAY)が用意されている。また、表示だけなら書式設定でもできる。しかし祝日となると、関数も何も、用意されていない。 祝日は年により、突然増えたり、「 …

no image

Access2013のWeb機能について(FileMakerと比較して)メモ

ここ数日、MS-Access 2013のWeb機能を勉強していたので、そのメモ。 ■フォームの書式 選択肢があまりに少ない。FMのようにデザインやテーマが用意されていない。 リスト表示のサイズが変更で …