Accessデータ処理のExcel VBAによる自動化

programming

なにをやりたいか？

Accessでクエリ・VBAを駆使して、txtやcsvのデータ処理（集計・変換etc）を行っている実務があるのですが、手作業で順番にデータのインポート・クエリの実行・マクロの実行・エクスポートなどを行っていたりして、時間もかかるしミスの可能性もあるので、自動化したいところです。
(環境はAccess2003）

今回は、Excel VBAを使って、Excelをコントローラとして自動化するようにしました。
なぜそのようにするかというと、以下の理由からです。

  • 前処理・後処理でExcelのマクロを動かすことがある
  • 複数のAccessを連携することがある
  • Accessの最適化は、Access自身から行うことが難しく、閉じた状態で外から実行する必要があるため

できることは以下の通り。他にもいろいろできると思います。

  • インポート・エクスポート（インポート定義・エクスポート定義の使用も含む）
  • SQLの発行 ("Delete * From Table1"　でデータを消したりとか）
  • クエリの実行
  • マクロの実行
  • VBA（モジュール）の実行
  • データベースの最適化

どうやってやるか？

１．Accessの参照を取得し、データベースを開く

まずは、Accessの参照を取得し、データベースを開くことが必要です。
以下のように記述します。(Microsoft Access x.x Object Libraryの参照設定が必要です）

Dim filename as string : filename = thisworkbook.path & "\" & "Test1.mdb"
Dim ObjAccess as New Access.application
objAccess.OpenCurrentDatabase(filename)
Dim ObjCmd as Access.Docmd
Set ObjCmd = objAccess.Docmd

２．いろいろ作業を行う

DoCmdオブジェクトを使用すれば、いろいろな動作が実行できます。

  • インポート・エクスポート：ObjCmd.TransferText
  • クエリの実行：ObjCmd.OpenQuery
  • マクロの実行：ObjCmd.RunMacro
  • SQLの発行：ObjCmd.RunSQL

その他、VBAの実行は、ObjAccess.Run で行えます。
データベースの最適化については後述します。
これくらいのことができれば、データ処理には十分かと。

（参考）DoCmdオブジェクト（基礎編）
http://www.geocities.jp/cbc_vbnet/kisuhen/docmd.html

３．使った参照の後始末

終了した後、参照を適切に破棄しないと、Accessのプロセスが残ってしまい、面倒になります。 ObjAccessを破棄するだけではだめで、ObjCmdもきちんと始末しないといけないです。
以下のようにDisposeプロシージャとしてまとめておきます。
無駄なコマンドがあるか、不十分な点があるかもしれませんが、とりあえずこれでうまくいきました。

Sub Dispose(ByRef ObjAccess as Access.application, ByRef ObjCmd as Access.Docmd)
Set ObjCmd = Nothing
ObjAccess.CloseCurrentDatabase
ObjAccess.Quit
Set ObjAccess = Nothing
End Sub

以下のように呼ぶことで、ObjAccess,ObjCmdを後始末します。

Call Dispose(ObjAccess, ObjCmd)

（参考）プロセスが残る
http://homepage1.nifty.com/rucio/commu/ThreadDetail_ThreadId_9482.htm

いくつかのポイント

これらが結構重要だったり。

Accessのセキュリティ設定を低くしておく。

これをやっておかないと、Accessを開くたびに、セキュリティのダイアログが出ることになってしまいます。

Application.DisplayAlertsをFalseにしておく

Accessで重い作業を行うと、Excel側で「別のプログラムでOLEの操作が完了するまで待機を続けます。」というダイアログが出て、OKを押さないと次に進まないことがあります。
これは邪魔なので、以下のように作業中はApplication.DisplayAlertsをFalseに設定します。

Application.DisplayAlerts = False
'（作業いろいろ）
Application.DisplayAlerts = True

(参考）"別のプログラムでOLEの操作が完了するまで待機を続けます"のエラーが発生するのはなぜですか？
http://www.mathworks.co.jp/support/solutions/ja/data/1-BE1O6T/index.html?product=EL&solution=1-BE1O6T

最適化を行うには？

最適化しないと、mdbのサイズがどんどん大きくなってしまいます。
大量のデータを扱う場合、適宜最適化を入れないと2GB制限を超えてしまうこともあり、作業の中で最適化を行う必要がある場合があります。
(そんな大量のデータを扱う場合にはAccessを使うなという話もありつつ・・・）

Webで検索すると、DBEngine.CompactDatabaseを用いる方法がよく出てきます。 でも、試して見た限り、使ったあとにMSACCESS.EXEというプロセスが残ってしまい、エラーの原因になることがありました。
なので、「閉じる時に最適化する」オプションを設定して閉じて、開いた後にオプションをFalseにするというなんとも素朴な策をとることにしました。

'閉じている状態のデータベースの最適化を行う
Sub Optimize(filename as string)
Dim ObjAccess as New Access.application



objAccess.OpenCurrentDatabase(filename)
Call ObjAccess.SetOption("Auto Compact", True)
ObjAccess.CloseCurrentDatabase



objAccess.OpenCurrentDatabase(filename)
Call ObjAccess.SetOption("Auto Compact", False)
ObjAccess.CloseCurrentDatabase



ObjAccess.Quit
Set ObjAccess = Nothing
End Sub

参考文献