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