MS AccessのインストールされていないPCで、VBA(Excel)を使って accdbを最適化する
最近VBAネタが続く・・・
今使っているOffice 2013を買う時、Accessはあれば便利かもしれないけど、
「どうせ使う機会は無いだろう」という事で、Office Personalを買ったので
Accessは持っていない。
しかし、最近になって〇〇なデータ管理をしようかと思ったが、どうにもExcelだけでは厳しい。
ローカルで手軽に使えるデータベースが欲しいけれど、このためだけにAccessを買うというのももったいない。
(LibreOffice Calc Baseという選択肢はない。)
バックエンド用として使えれば、フロントエンドはC#とか最悪Excelでもなんとか出来る。
あれ、mdbってVBAで作れなかった?
やっぱり作れるじゃないか、accdbも!
で、このゴールデンウィークに、accdbファイル自体と、
それに、テーブルを作るためのツールを作った。
肝心のデータ管理用のフロントエンドは全く出来ていないし
データもインサートしてないのに、気が早くて「最適化ツールは絶対必要だ!!!」
となり、作り始めたはいいが、コンパイルエラー
"DBEngine"を、「変数が定義されていません」とおっしゃる。
DAOの参照設定してないじゃん。
Microsoft DAO 3.6 Object Library 参照設定して、コンパイルもOK。
いざ、実行!
実行時エラー 3343
データベースの形式 'C:\Datas\sample.accdb' を認識できません。
調べていくと
引数 options に次のいずれかの定数を使用すると、最適化されたデータベースのデータの形式のバージョンを指定できます。
DBEngine.CompactDatabase method (DAO) | Microsoft Docs
・・・
dbVersion120
最適化に Microsoft Access データベース エンジン バージョン 12.0 のファイル形式を使用するデータベースを作成します。
オプションに、dbVersion120を追記して、コンパイルしてみる。
"dbVersion120"に対して、"変数が定義されていません。"
まだ、だめですか・・・orz
さらに調べていくと、
DAO 12.0 (ACEDAO.DLL) の DBEngine.CompactDatabase メソッドを利用する。
VBレスキュー(花ちゃん) の Visual Basic 2010 用 掲示板(VB.NET 掲示板)
という記載を発見。
でも、参照設定に、DAO 12.0なんて見当たらない。
そもそも、ACEDAO.DLLなんて入ってるの?
DLLはあった。
じゃあ、どれを参照すればいいの???
オブジェクトブラウザーで「DAO」を確認すると「Microsoft office 15.0 Access database engine Object」に含まれているんですね。
ふひろ通信: Microsoft Access 2013の参照設定で「Microsoft DAO 3.6 Object Library」を有効にしようとすると警告が出る
これ(Microsoft Office 15.0 Access database engine Object Library)か?
参照設定チェックしてみると
オブジェクトブラウザに、dbVersion120の文字が表示された!
コンパイルもOK。
いざ、実行。
エラーの発生もなく、無事終了。
ダミーのデータ10万件をinsert後、全てdeleteしてから
最適化処理でファイルサイズが小さくなっているのを確認。
(拡張子".tmp"となっているのが最適化実行直後(リネーム前))
最終的なコードは、これ
'参照設定 'Microsoft Office 15.0 Access database engine Object Library Public Sub compactDB() Dim sSrcPath As String Dim sDestPath As String '最適化するファイルのパス sSrcPath = "C:\Datas\sample.accdb" '最適化後のファイル名 sDestPath = sSrcPath & ".tmp" On Error GoTo ERR_COMPACT_DB '最適化の実行 DBEngine.CompactDatabase sSrcPath, sDestPath, DAO.LanguageConstants.dbLangJapanese, DAO.DatabaseTypeEnum.dbVersion120 '冗長に書いているが、下記でもOK 'DBEngine.CompactDatabase sSrcPath, sDestPath, dbLangJapanese, dbVersion120 '最適化前のファイルを削除 Kill sSrcPath '最適化後のファイル名を最適化前のファイル名にリネーム Name sDestPath As sSrcPath ERR_COMPACT_DB: If Err.Number <> 0 Then Debug.Print "[" & CStr(Err.Number) & "]" & Err.Description Else Debug.Print "Done." End If End Sub
自分一人で使うやつなので、エラー処理は最低限。
あと、参照設定の15.0の部分は、Officeのバージョンで変わるらしい。
mdbの場合は、dbVersion120の代わりに、dbVersion40を使えばいけるのかな?(未確認)
参考サイト
DBEngine.CompactDatabase method (DAO) | Microsoft Docs
DatabaseTypeEnum enumeration (DAO) | Microsoft Docs