AccessのデータをExcelで利用する時に気づいたこと

皆さんこんにちは。スマホやPC画面ばかり見てると疲れますよ。たまに窓の外を眺めてみるのが良いですね。(というこじつけの見出し画像💦）

Accessに収納されているデータをピボットテーブルを使って見やすく表示したい。そういう時ありますよね。

かつてはAccessの中にもピボットテーブル機能が存在していたのですが、いつの間にか無くなったようです。20年ほど前に作ったデータベースにピボットテーブルで表示する画面がありました。集計の方針とかが変更になって使わなくなっていたのですが、別の用途で使えるかなと、今年Access2019で開こうとしても開かなくなっていました。

じゃあこのデータベースどうしたら良いの？　使えないの？　と、途方に暮れかけたんですが、ピボットテーブルはそもそもExcelのモノだからExcelで使えってことだな。と切り替えて、AccessのデータをExcelにインポートすることを考えました。

まずひとつ目の利用形態。

Accessで現金仕入れや交通費などの経費を、現場ごとに分類して入力したものをExcelピボットテーブルで、月別、現場別に集計する。

これはExcelの「データの取得」を使えばできますね。ただし、Accessが開いているとインポートができないので、Accessは閉じておきます。

別の利用形態。

今度は20年前から使っている顧客データベースの中にあるデータを使って、売り上げの傾向を分析したい。

同じようにExcelから「データの取得」を試みたところ、データベースに設定してあるパスワードのためにエラー。

これを回避する方法を調べて、パスワードは設定したままデータ取得に成功。ピボットテーブルを複数作成して完成！

しかしテスト運用中に不具合が、、、。

Accessを開いているとデータの更新が出来ないのは同じだけれど、Excelのほうを開いていると、データベースを開いたときに読み取り専用の状態になってしまう。排他制御されているんですね。

会社としては利用頻度が高く、全員が利用する顧客データなので、この状態はマズイ💦

回避策はAccessからのエクスポートか。

1から作り直しになるけれど、エクスポートの設定を始めた。

エクスポート操作を保存して、マクロに登録。エクスポートしたいデータのフォームを閉じたときにマクロを実行してエクスポートするようにした。

エクスポート先のExcelブックがどのように更新されるのか、ブックを開いた状態でエクスポートされたらエラーが出るかなど確認したが、特に問題は見当たらず。だが念のため、ピボットテーブルは別の新しいブックに作ることにした。こうすればエクスポート先のブックを開く必要はなく、データの受け渡し専用になるから。

まとめ

今回の経験で得たものをまとめると、

AccessからExcelにデータを渡す場合に、Excel側でインポートする際にはAccessは閉じていなければならない。

インポートしたExcelファイルを開いていると、Accessを開くとき読み取り専用になる。

Accessにパスワード設定がされているとエラーになることがある（パスワードの設定変更が必要）。

Accessからのエクスポート時、エクスポート先のブックにある他のシートには影響がない。複数のエクスポートをひとつのブックに収めることが可能。

以上、わかり難くてごめんなさい。失礼しました。