ExcelリンクしたAccessが遅い原因を対策
社内には様々な業務がありその中には、特定個人が入力し報告レポートを上げるだけの仕事も存在しています。
Excelでいいのではないかという意見もわかりますが、自分はExcel帳票を信用していません。
ちゃんとしたレポートであればAccessレポートを推奨しています。
Accessレポートであれば再利用性が高いので、データさえちゃんとしていれば2回目以降はスムーズに帳票を出力できるようになります。
問題は入力のフォームをどうやって作成し、正しい型のデータを作成するかが課題になります。
VBAでフォームを作ってもいいのですが、対象ユーザーが一人であり、凝ったVBAにするとメンテナンスが大変になります。
AccessにはRDBとネイティブに接続するリンクテーブルという機能があります。
通常は他のAccessファイルやDBと接続しますが、Excelでも接続できます。
不具合なく接続するには凝ったシートにはできませんが、それでもExcelからリアルタイムにデータを取得できる効果は大きいです。
そのため最近ではExcelに入力してもらい、Accessのリンクテーブルでデータ連動をさせて、クエリとレポートを使って帳票を作るようにしました。
効果は抜群でメンテナンスの手間が圧倒的になくなりました。
そんな訳からExcelシートとAccessテーブルがリンクしたマイクロサービスがあります。
問題発生
作ってから数年が経過した際に「最近レポートの出力が遅いんだけど・・・」と問い合わせがありました。
レポートを開いてみるとたしかに遅いです。
最適化を実施
Accessのクエリを使ってデータ成形をしています。
クエリはその特性上Access内部に何らかのデータを保持します。
それが時間経過に伴いデータ肥大化を引き起こしている可能性があります。
情報 - 最適化と修復を選択します。
結果は変わらず
テーブルの中身を確認してみます。
すると
データがあるのは100行くらいなのに、テーブルには1,048,574行があると言っています。
100万行もあるデータからクエリで副問合せなどをしているので、レスポンスが悪くなったようです。
テーブル変更
どうすれば空行が作られないか検証してみます。
まずテーブルデザインから「値要求」をいいえから「はい」に変更してみます
が、フィールドの値要求を「はい」にできない
リンク先がExcelの影響でExcel側の情報を色濃く引き継がれておりテーブル情報は変更できないようです。
以上を踏まえ
真の原因
Excel内にデータは無いが、行が割り当てられていると推察ができる。
これはExcelとAccess間の問題ではなくExcelの問題。
なにかの拍子に最大行に値を入力してしまうと、最大行までデータの器を用意してしまいます。
その器をAccessが見て最大行のリンクテーブルを作ってしまうという流れになります。
対策
無駄な行を削除する
入力するであろう行より余裕をみて、それよりも後ろの行は削除して構いません。
ただExcelの仕様で実際に行は消えず、残っていますが内部的には消えているので問題ありません。
将来の対策
シートの保護で必要以上を入力させないようにロックする
Excelで入力をしている以上Ctrl+↓などで最大行まで移動してしまいケアレスミスは発生してしまいます。
ミスを防ぐにはシートをロックして簡易的に移動できないように対策するのが手っ取り早いです。
入力してほしくない行を選択しセルの書式設定を選択
保護 - ロックにチェックが入っていることを確認する
校閲 - シートの保護を選択
ロックされたセル範囲の選択のチェックを外す
これで選択した範囲を入力できなくなり、操作ミスで行データを作ってしまうのを防げます。
