1
4
古いDBからExcelVBAでExcelにレコードを書き込むツールを作ってみたのですが、かなり重く悪戦苦闘しています。
現場のプロのみなさんはどう対応されてるかお伺いしたいです。
【状況】
2005年に作成されたAccess2000形式の拡張子「.mdb」のデータベースで、再構築した方が早そうなのは重々承知なのですが、他部署の営業が使ってるということでDBの再構築が叶わない状況です。
※Accessは32bitの2019版。win10の32bit。共有ドライブ上での使用。
※自身は1部署の効率化ツールを作成するために派遣で常駐しています
【ExcelVBA実行時の状況】
・初回の実行が4，5分かかります。しかし、2回目以降は数秒で該当のレコードをExcelシートに書き出すことができます。
・上記はaccessを開いた状態です。開いていないと、2回目以降も遅いです。アクセスを開いてクエリ実行完了までに5分くらいかかります。
【なぜ、実行準備が整うまで時間がかかるツールを使うのか】
2回目以降は数秒で取得できるため、処理件数（Excelのリストシート）が多いほど手動でやるよりも早く終わるからです。
※該当の顧客レコードを取得し、整形・加工して印刷とPDF保存を行うツールです
【ExcelVBA側のドライバ】
AIに聞いたところ、12.0の方がいいよってことなので、それを使ってます。
AI「Microsoft.Jet.OLEDB.4.0 は公式にはサポート終了しているため、可能であれば Microsoft.ACE.OLEDB.12.0 を使うのが推奨されます。
32bit 環境でも、Access Database Engine 2010 (32bit版) をインストールすれば、以下のように ACE.OLEDB.12.0 を使うことができます。」
【やってみたこと】
DBの最適化のみ（無駄クエリ、無駄テーブル等の削除行為は他部署のツールだからできない）
【DBの様態】
実際にデータが入っているDBと、フォームがあるDB（リンクテーブル）は分割されている
【知りたいこと】
経験豊富なプロの方たちはもし上記の状況だった場合、どうされますか？？
この状況は詰んでいますでしょうか？
これ以上やりようが無いか確かめたく質問させていただきました。
【上記の状況を要約】
勝手にDB再構築できない、レポートのレイアウトや、フォームの機能変更はできない。レコードを取得するのみならず更新作業もある。でも、事務作業に時間とられたくないから何とかならないかという依頼を受けている。
回答16件
#1
総合スコア1525
投稿2025/04/05 20:37
私の今の職場でも、mdb使用しています。特別遅いと思う事はありません。
一回目が遅く二回目が遅くない。ただしAccessが起動していたら。とのことですが
Accessが起動していたら、という部分の理由がよくわかりませんが
ぱっと想像するに「実行準備が整うまで時間がかかる」という事は
その実行準備の処理で何か遅い原因となる処理があるという事なのでしょうか。
普通に考えると、大きなネックとなりそうなのは結合の所でしょうか。
その場合、インデックスを設定すると劇的に早くなることがあります。
インデックスも付与できない場合であれば、結合するのはあきらめてDictionaryを使用して
自前で結合することを視野に入れると良いかもしれません。
とりあえず、ステップ実行で、何の処理が遅い要因かを絞り込み
その要因を提示して頂ければ、解決策も出てくるのでは。と思いもします。
#2
総合スコア25430
投稿2025/04/06 02:32
編集2025/04/06 02:342回目以降は数秒で取得できるため、処理件数（Excelのリストシート）が多いほど手動でやるよりも早く終わるからです。
※該当の顧客レコードを取得し、整形・加工して印刷とPDF保存を行うツールです
データの利用としては、照会のみのようですので、遅い１回目を回避すれば良さそうですね。
共有ドライブ上での使用。
との事なので、初回が遅いのはネットワーク環境の影響を受けている事が予想されます。
初回の実行が4，5分かかります。
初回時はMDBを共有モードでopenするのにそれだけ時間が掛かっているという事だと思いますので、excelツールでは初回に必要なファイルをローカルにコピーして、そちらを参照するように変更してはどうでしょうか？
ただ、これはあくまでネットワーク環境でのMDBのOPEN時間より、ファイルコピー+ローカルでのOPENの時間が小さくなるだろうという想定によるものです。
#3
xail2222さん、ありがとうございます。
インデックスの設定はデメリットを確認したところ、更新作業に影響があることが分かり設定していない状態です。
AccessDBは分割されており、メインテーブルが100万行弱、サブテーブルが60万行超ほど。
「Accessを開いてると」というのはフロントエンドのDBを開いてると2回目以降のクエリ実行が早いということです。バックエンドのDBは開いても閉じても遅いです。
ステップ実行は下記の部分で待たされます（待っていればデータ取得は完了します）。
' レコードセットを開く Set RS = CN.Execute(SQL)
下記の典型的なADO接続のコードをベースに作成しています。
顧客レコードを取得するときにJOIN、UNIONは使用していません。
SQLは実際の検索キーワードはStringに変更してます（WHERE条件）。
至ってシンプルなSQLです。検索条件を追加しても削除しても遅い状態です。
Sub Test()
Dim CN As Object ' ADODB.Connection
Dim RS As Object ' ADODB.Recordset
Dim SQL As String
Dim AccessFilePath As String
Dim ws As Worksheet
Dim SearchID As Long ' 社員番号
' ワークシートを設定 Set ws = ActiveSheet ' A1セルの値（検索する社員番号）を取得し、数値型に変換 If IsNumeric(ws.Cells(1, 1).Value) Then SearchID = CDbl(ws.Cells(1, 1).Value) Else MsgBox "A1セルに有効な社員番号を入力してください（数値のみ）。", vbExclamation, "入力エラー" Exit Sub End If ' Accessファイルのパスを指定 AccessFilePath = "********************************DBADO_TEST.mdb" ' ADO接続を作成 Set CN = CreateObject("ADODB.Connection") CN.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AccessFilePath ' SQLクエリ SQL = "SELECT * FROM T社員名簿 WHERE 社員番号 = " & SearchID ' レコードセットを開く Set RS = CN.Execute(SQL) ' 既存のデータをクリア（B2:H2の範囲） ws.Range("B2:H2").ClearContents ' データが存在する場合のみ処理 If Not RS.EOF Then ' B2セルからデータを書き込む Dim i As Integer For i = 0 To RS.Fields.Count - 1 ws.Cells(2, i + 2).Value = RS.Fields(i).Value Next i Else ws.Cells(2, 2).Value = "該当データなし" End If ' クローズ処理 RS.Close: CN.Close Set RS = Nothing: Set CN = Nothing
End Sub
#4
saziさんありがとうございます。
社内からAccessDB（DB分割されてる）が重いと相談があり、「ExcelからADO接続でDBを開かずにバックエンドのDBからデータ取得すればいいじゃん」と安易な発想で引き受けたのが今回の相談の由来です。
しかし、色々試したところ、フロントエンドDBの2回目クエリ実行からストレスない速度でレコード取得できることが判明しました。
そして、本来はDBを開かずに取得したいという目的でしたが、時間的制約も多少ありますのでDBは開く方向でAIにこう聞きました。
「アクセスDBで開いてる時は問題なくADOコネクションをExcelVBAで実行できる（2回目のクエリ実行から）のにDBを閉じると、RS.Open のレコードセットオープンのところで、ずっと実行中になり固まってしまうのは何故⁇」
【AIの回答】
① Accessのロックファイル（.ldb / .laccdb）の扱い
Accessを開いているときは、ロックファイルが正常に管理され、外部からの接続も安定して行われる。 しかし、Accessを閉じると、ロックファイルがうまく扱えずにADOの接続が不安定になることがあります。
② クエリやテーブルの依存関係
ADO経由で開こうとしているテーブルやクエリが、フォームや他のオブジェクトに依存している場合、Access本体が開いていないと参照できない場合があります。
特にフォームでフィルタリングしてるクエリ、TempVars、VBA関数などを使ってる場合。
③ OLEDB接続の初期化の問題
Accessが閉じていると、OLEDBエンジンがAccessの処理を一部再現しなければならず、うまく初期化できないことがあります。
【AI別回答】
Accessがローカルキャッシュを使用していた
Accessは、開いている間はデータをキャッシュしてスムーズに動作しますが、閉じると直接ファイルを読み込む必要があり、時間がかかることがあります。
対策:
Jet OLEDB:Database Locking Mode=0 を接続文字列に追加して、最適化を試す。
そして、SAZIさんが言われたことも的を得てると思います
【AI別回答】
4. ネットワーク・共有フォルダの影響
Access データベースがネットワークドライブや共有フォルダ上にある場合、アクセス状況によってはレスポンスが異なることがあります。特に、Access アプリケーションが開いていると Windows の SMB キャッシュが有効になるため、データの読み取りが高速化する可能性があります。
ただ、やはり、初回クエリ実行がキャッシュに残ってるから2回目以降が早いのかなと思えてきました（憶測です）。
それに直接バックエンドDBにクエリ実行した場合、開いてても2回目以降が遅いのは謎です。
#5
総合スコア25430
投稿2025/04/06 05:22
編集2025/04/06 05:51メインテーブルが100万行弱、サブテーブルが60万行超ほど。
この状況で適切なインデックスが無いなら、初回のDBのOPENに係る時間だけでなくクエリーにも時間を要している場合もありそうですね。
２回目以降は多分キャッシュ効果でしょう。なので初回の読み込みを早くしないと意味は無さそうです。
インデックスの効果を確認するためには、DBファイルをローカルに配置して確認すると良いでしょう。
社内からAccessDB（DB分割されてる）が重いと相談
これは、以下の何れでしょうか？
①次第に重くなってきた
②いつからか重くなった
③当初より重かった
それぞれで以下の対策が考えられます
①利用しない古いデータを定期的にバックアップし削除。
②OSのアップデートなどにより環境が変わった可能性があるので、アプリを再構築
③アプリを再構築
#6
総合スコア1525
投稿2025/04/06 05:40
インデックスは、主キーの方ではなく、各列に設定できる（重複ありでも良い）やつですが
これもダメでしたか？
確かに更新処理が遅くなるという問題があるのは事実ですけどね。
あと「Accessを開いてると」というのは接続、connectionをOpenしたまま。という事ですか？
それともAccessアプリケーションを開いているとという事ですか？
それとコードの提示ありがとうございます。
「SQL = "SELECT * FROM T社員名簿 WHERE 社員番号 = " & SearchID」のＳＱＬの実行で遅いということですが
これは、テーブルを変えても一回目は遅いのでしょうか。
それとも「T社員名簿」の検索一回目が遅いのでしょうか。
また「T社員名簿」は実テーブルですか？リンクテーブルですか？
クエリではないですよね。
色々試してみましたが、なかなか遅いという状況を再現出来ないですね。。。
#7
saziさん追加でありがとうございます。
この状況で適切なインデックスが無いなら、初回のDBのOPENに係る時間だけでなくクエリーにも時間を要している場合もありそうですね。
そうです。OPEN時のクエリ実行が完了して、Accessフォームから検索しても初回実行は遅いです。
インデックスの効果を確認するためには、DBファイルをローカルに配置して確認すると良いでしょう。
そうですね。いままで下記のインデックスのデメリットで忌避してましたが、一回はやってみようと思います。
AI「インデックスは、追加・更新・削除のたびに自動で更新されるため、レコード数が多いと、書き込みパフォーマンスが落ちます。」
実際やってみて、メリット・デメリットを比較衡量して決めたいと思います。
※プロジェクト全体で、この問題解決後にレコード更新機能を実装する予定
ただ、もしローカルで高速化したら、あとはどうするか問題がでそうです。
顧客情報が詰まったDBは仮想・共有ドライブのみならず、パスワードがかかった認証端末からしかアクセスできないドライブに置くという決まりがあるので（開発中はコピーを共有ドライブ個人フォルダに暗黙の了解で置かせてもらってます）。
配布型のフロントエンドＤＢをローカルに置くのも厳しい環境です。
八方ふさがりになったら、情シスにＤＢ再構築して良いか進言してみます。ありがとうございます。
#8
xail2222さんありがとうございます。
インデックスは、主キーの方ではなく、各列に設定できる（重複ありでも良い）やつですが
これもダメでしたか？
インデックスはデメリットを鑑みて設定していない状況です。週明け、試しにやってみようと思います。
あと「Accessを開いてると」というのは接続、connectionをOpenしたまま。という事ですか？
それともAccessアプリケーションを開いているとという事ですか？
アプリを開いてるということです。
「SQL = "SELECT * FROM T社員名簿 WHERE 社員番号 = " & SearchID」のＳＱＬの実行で遅いということですが
これは、テーブルを変えても一回目は遅いのでしょうか。
それとも「T社員名簿」の検索一回目が遅いのでしょうか。
このコードはベースにしたコードですので実際のテーブル名とは違います。
検索のデータ型が文字列になってシングルクォーテーションが付いただけです。
SELECT * FROM 100万行弱のテーブル 顧客コード = '" & SearchID & "'"
色々試してみましたが、なかなか遅いという状況を再現出来ないですね。。。
私も自宅PC（win11、64bit）でAccess2021 64bit で再現しようとしましたがサクサク動いて無理でした。
20年使ったオンボロDB、32bitアプリ、共有ドライブの空き容量５G未満という劣悪な環境の実現は不可能と悟りました。
#9
総合スコア25430
投稿2025/04/06 06:42
私も自宅PC（win11、64bit）でAccess2021 64bit で再現しようとしましたがサクサク動いて無理でした。
では、共有ドライブがネックになっているのは自明な気がします。
共有ドライブとしているのは、物理的にはどのようなものなのでしょうか？
例えば遠地にあるファイルサーバーなどでしたら、ネットワークのスペックを上げるのが効果的ですし、セグメント内にあるPCのドライブという事でしたら、ストレージがSSDの新しいPCにすると効果的だと思います。
ネットワークだと厄介ですけど、新PCの調達位だったら、人件費より安く済むはずですので。
#10
SAZIさん、すみません。
この部分を読み飛ばしていました
これは、以下の何れでしょうか？
①次第に重くなってきた
②いつからか重くなった
③当初より重かった
異動もあるため20年いる職員さんがおらず、いつからか不明です。ただ、コードを見ると外部委託で作成されたことが読み取れるので当初から重いDBではなかったと考えられます。
それぞれで以下の対策が考えられます
①利用しない古いデータを定期的にバックアップし削除。
②OSのアップデートなどにより環境が変わった可能性があるので、アプリを再構築
③アプリを再構築
③が妥当といいますか、それしかないように思えてきました。
ネットワーク・サーバーは常に障害が発生してるわけではなく、時間経過で改善される（それでも他の会社と比べれば脆弱です。サーバーダウンでたまにUiPathのRPAが停止。VBAのRPAはなおさら止まる）。それにそもそもインフラに関与する権限がありません。。。
#11
総合スコア25430
投稿2025/04/07 02:00
編集2025/04/07 02:12要求事項として、ネットワーク経由でアクセスするDBファイルに対する処理性能の最適化という事で考えた場合の１番のポイントは、DBファイルに関してのアクセス頻度を下げる事です。
DBファイルが一つなら良いですが、マスター用、トランザクション用など複数のDBファイルで構成されているような場合、リンクテーブル等を使用すると格段に性能が低下しますので、IN 句の利用をお薦めします。
IN句はFrom句で使用するので、参照するテーブルが多くなると、SQLは必然的にネストが多くなりますので、使用するビューをしっかり設計（DBファイル上に照会用のクエリーを配置する事も含む）しないと手戻り等で開発効率が悪くなります。
※私自身この要求事項に対応する事はしばしばあり、DBファイルに関するアクセスはクラス化して実装しています。
#12
総合スコア269
投稿2025/04/08 00:16
AccessからExcelへ直連携させるのではなく、Accessからいったん、テキストファイルにエクスポートして、テキストファイルからExcelにインポートするようにすれば、早くできるように思います。
#13
総合スコア3020
投稿2025/04/08 00:37
現状、「フロントエンドの初回が遅い」という部分について有効な回避策がないようですので、別案のご提案です。
「他部署の管理しているDB」という部分での制約も多いようですので、いっそのこと自部署用のコピーDBを用意しては如何でしょうか？
リアルタイムではなくなりますが、タスクスケジューラなどで定期的にバックエンドのDBから自部署用DBに差分コピーを取って利用するイメージです。
これが可能なら、自部署用のDBでは好きにインデックスもはれますし、アクティブなデータと非アクティブなデータでテーブルを分けるなどして参照するデータ量を減らすようなこともできるかもしれません。
他部署の共有フォルダまでに線の細いネットワークを介しているようであれば、自部署内のネットワーク的に近い場所にDBをおけるというメリットも出てくるかもしれません。
根本対応ではありませんが、自分ならどう要望に応えるか、という観点で回答させていただきました。
#14
saziさん、pyon_kiti_jpさん、jawaさん、ありがとうございました。
いろいろ、試してみましたが、一旦、キャッシュを敢えて作成して実行するやり方を暫定とし、この件は今後の課題にしてクローズさせて頂きます。
多角的にアドバイス下さり、色々勉強になりました。ありがとうございます。
【プロジェクトの全体】
1.ユーザーにリストシートへ顧客コードの入力、団体名を選択、状況区分変更（変更がある場合）の入力をしてもらう
2.Aデータ取得：特定のフォルダ内にある最新のExcelファイルから顧客コードに一致した該当行の印刷とPDF保存。
さらに後工程で必要なデータ（顧客名等）をリストシートに代入。もし、見つからなかった場合は先月のシートを参照
3.上記をリスト最終行までループ
4.ユーザーに専用DBを開いてもらい、キャッシュ作成の為の仮実行（ダミーデータ）ボタンを押下してもらう ←相談箇所
5.Bデータの取得：顧客コードですべての契約年度のレコード（メインテーブル）と、契約変更履歴のレコード（サブテーブル）をExcelTempシートに代入
6.整形・加工した ①当該年度の顧客状況詳細 ②顧客レコード一覧（すべての契約年度） ③変更履歴一覧 を印刷、PDF保存
7.上記5~6をリスト最終行までループ
8.別DB（仮更新DB）に顧客の状況区分変更の更新をする。別DBにレコードがない場合INSERT ※別部署が精査後にメインテーブル更新）
9. 1.で取得したデータを元に、専用Webサイトから顧客の案件番号を順にスクレイピング（このDBに接続できない事情）。案件番号を名前として保存したPDFに付与
10.案件番号ごとにクラウドにバインダーを作成し全件アップロード（別ツール）
【やってみたこと】
・インデックス作成
・CSVでエクスポートして、CSVにADOコネクション
・別途作成したDBにメイン、サブテーブルごとインポートして、ADOコネクション
・Excelにテーブルをまるごとインポートしてから該当レコード抽出（For Each）
【わかったこと】
別DBにテーブルを移しても遅い。1万件満たないDBからは閉じたまま同じコードでサクッと抽出できた
顧客コードに一致するすべてのレコードを抽出したいので、他の条件を足しても速度は変わらなかった
#15
総合スコア25430
投稿2025/04/14 01:42
OpenRecordsetでdbOpenSnapshotを指定すると多少は改善するかもしれません。
#16
解決しました！
他部署DBの更新頻度が頻繁・不定期ではなく、1か月ごとに更新ということが判明したため、月１で新たに作成した.accdbにインポートすることにしました（インポート自体は時間がかかりますが）。
差分インポートも検討いたしましたが、他部署DBということもあり、テーブルに変更があったら嫌だなと思い、インポート時に既存テーブルを削除し、CREATE INDEXを行う仕様にしました（コピーしたソースDBにインデックスを作成しても変わらぬ重さであったため）。
ありがとうございました。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。