Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 7 Professional : Excel 2010)
mdbファイルの最適化でエラー
投稿日時: 18/11/23 18:27:47
投稿者: ビタミンVBA

いつもお世話になります。1年ほどまえに使えた(と記憶)マクロがエラーになり、困っています。
アドバイスをよろしくお願いします。
 
Excel2010:Accessは元のExcel2000時のAccess2000のみ
Windows 7
 
mdbファイルをVBAで最適化するマクロです。
参考にさせていただいたサイトは下記です。
ADO版:[White Tiger]
http://www7b.biglobe.ne.jp/~whitetiger/ac/ac2002058.html
参照設定:[Microsoft Jet and Replication Objects 2.6 Library]
 
DAO版:[T'sWare]
https://tsware.jp/study/vol1/kaibo_09.htm
 
Sub MDB最適化()
   Dim Jrojet As New JRO.JetEngine
   Dim strCon As String
   Dim myPath As String, tmpPath As String
 
   On Error Goto ErrHandler
 
   'ADOコネクション文字列指定
   strCon = "Provider =Microsoft.ACE.OLEDB.12.0;Data Source =" '(1)
   'strCon ="Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" '(2)
 
   '最適化前のmdbファイル
   myPath ="C:\Test\myDB.mdb"
   '最適化後のmdbファイル
   tmpPath ="C:\Test\tmpmyDB.mdb"
 
   '最適化実行(この実行でエラー)
   Jrojet.CompactDatabase strCon & myPath, strCon & tmpPath 'ADOの場合
   'DBEngine.CompactDatabase myPath,tmpPath 'DAOの場合
 
   '元ファイルを削除
   Kill myPath
 
   '最適化後のファイル名を変更
   Name tmpPath As myPath
 
   Exit Sub
   '------------------------------
ErrHandler:
   Debug.Print Err.Number;"/";Err.Description
   Stop
   Resume Next
End Sub
 
[状況]
1)元々はOffice2000(Excel/Access)で動いていたマクロですが、Excel2010とExcel2000を共存すると不安定な感じがして、今はExcel2010のみで動かしています。mdbファイルもaccdb形式に替えたほうがいいのかと思いましたが、accdbが開けないのでmdbのままにしています。この移行でファイルが破損しているのかもしれません。
Excel2000で作ったmdbファイルは今でも正常に開き、中のデータを見ることができますが、Excel2010で書き換えたmdbファイルは開くと「データベースを認識できません」というエラーになります。ただ、Windows10のPCのOffice365のExcel2016(Access2016あり)ではWindows7では開けないmdbファイルも開け、中のデータは正常に見られます。ただWindows10 PCでも最適化は同じエラーになります。
 
              Windows7 | Windows10
[Excel] 2000→2010にアップ(Access2000のみ) | Office365(Excel2016/Access2016)
[mdbファイル] 2010で書き替えたmdbは開けない     | 左のmdbファイルも開ける
[最適化]   2000のmdb/2010mdbともにエラー     | エラー
 
2)最初(1年前)はコネクション文字列は元のサイトの通り(2)のJetで行いましたが、(1)のACEでもうまくいったので、こちらにしました。今回はどちらもエラーになります。
3)その後、あまり最適化は実行していなかったのですが、今回やってみるとエラー「引数が無効です」となります。
4)DAO版でもエラーとなります(データベースの形式を認識できません)
5)Office365は2台インストールできるのでWindows7も切り替えてもいいのですが、最適化がどちらもエラーになるので踏み切れません。
 
よろしくお願いします。
 
 
 
 
     
 

回答
投稿日時: 18/11/23 21:18:58
投稿者: WinArrow
投稿者のウェブサイトに移動

疑問点
   
  (1)JROでは、ACE.OLEDB.12.0
は、使えない?・・・・
使ったことがないので
JET.OLEDB.4.0
で、試してみてください。
  
ACE.OLEDB.12.0
を使う場合は、対応が違うように思います。
でも、ファイルが.mdbなので、JETエンジンを使った方が無難でしょう。
エラーになるのは、次の(2)が要因かも?
    
(2)JROでは、ロケールを追加したほうがよい
   
myPath ="C:\Test\myDB.mdb;Locale Identifier=1041"
    

投稿日時: 18/11/23 21:39:52
投稿者: ビタミンVBA

WinArrowさん、早速のアドバイスありがとうございました。
 
(1)ExcelではACE.OLEDBよりもJetのほうがいいのでしょうか。参考書を見ると新しい例ではACEのほうをよく見るようになったので、Excelもバージョンが新しい場合はACEのほうがいいのかなと最近はJetからACEに変えていますが、ExcelはJetのままでもいいのでしょうか。実はExcel2003からExcel2010に移行したら、応答なしが頻繁に起こったので、最初からExcel2010でACEで作り直したら頻度が減ったような気がしたので、ACEを使うようになりました。
 
(2)myPath="C:\Test\myDB.mdb;Locale Identifier=1041" をやってみましたが、エラー内容は変わりませんでした。
 
(3)以前に作ったmdbをEcel2000/2003で作ったファイルがあったので、それを最適化したら正常に動作しました。
Jet.OLEDBで作り、Jrojetで最適化したものでした。
 
最初からACEでなく、Jetで作ったら行けるのかもしれません。大幅な変更になりそうなので、区切りのいいところで再度考え直してみます。

回答
投稿日時: 18/11/23 22:57:20
投稿者: WinArrow
投稿者のウェブサイトに移動

>最適化
については、
ExcelVBAで最適化プログラムを作成しなくても
データベース側で、
「閉じるときに最適化する」設定ができます。
 
 
 
 

回答
投稿日時: 18/11/24 10:38:41
投稿者: hatena
投稿者のウェブサイトに移動

最適化は、DAOやADOのCompactDatabaseの他に、Access.Application の CompactRepairメソッドでも可能です。(Accessがインストールされいる必要がありますが。)
 
CompactDatabase と CompactRepair では、後者の方が最適化後のサイズが小さくなる場合が多いので、私の場合は、CompactRepair を使ってます。
 
Accessデータベースファイルを最適化する関数 - hatena chips
https://hatenachips.blog.fc2.com/blog-entry-378.html
 
下記はAccessでのコードですので、エクセルからは Access.Application を CreateObjectするか参照設定する必要があります。
 
ご参考までに。

投稿日時: 18/11/26 07:09:56
投稿者: ビタミンVBA

WinArrowさん、hatenaさん、アドバイスありがとうございます。
 
WinArrowさん
>「閉じるときに最適化する」設定ができます。
Access2016がある方のPCでオプションで最適化のチェックを入れたら、エラーなく動作しているようです。
最適化前のサイズを確認していなかったので、小さくなったかわかりませんが、類似の別のmdbファイルよりも小さくなっているので、オプションでの設定はいけるのかなと思います。
 
hatenaさん
AccessのないPCの方を主体で作っていますので、CompactDatabaseでトライしてみたいのですが、再度Jetでmdbを初期から作って見ます。
 
 
アドバイスをいただきながら、すぐに確認できなくて申し訳ないですが、今の作業の区切りがつくのが数日かかりそうなので、その後で再度Jetでmdbファイルを作り直して、元の方法を確認してみます。
今までの経緯から
(1)1年以上前にExcel2003(excel2000のアップ)でJetで作ったmdbファイルはCombpactDatabaseが行けた(DAO/ADOとも)。今でもExcel2010で最適化できました。→Jetで作ればいける?
 
(2)Excel2010とExcel2003の共存は不安定なのでExcel2010にして、ACEでmdbを作り、作業を続けてきましたが、最適化は未確認だった(多分)ので、今回エラーに遭遇?切り替え途中でおかしな動きをしたことがありますが、データベースは動いたので、そのまま来ていますが、途中でファイルが破損したかも。
ACEにしたのは、Jetが古いと思い、Excel2010ならACEのほうがいいかと思ったので。
 
(3)再度Excel2010でJetでゼロからmdbを作り直して、やり直す。
というように進めてみます。
 
ここで結果をご報告するまで時間がかかるかもしれませんが、未解決のままにしますが、お許しください。
(以前、質問しながら体調不良で1年くらいあとでみたらトピックが消えていて失礼しましたが、そうならないように注意します)

回答
投稿日時: 18/11/26 13:17:13
投稿者: Suzu

こんにちは。
 

引用:
Access2016がある方のPCでオプションで最適化のチェックを入れたら、エラーなく動作しているようです。

 
このファイルを同時接続で、共用で使用するのであれば、お勧めできません。
誰かが開いているとエラーになりますよね。
(名前の自動修正 も外して置いた方が良いです。)
 
 
引用:
4)DAO版でもエラーとなります(データベースの形式を認識できません)

このファイル、元はいつ頃、何で作成されましたでしょうか。
同じ「mdb」でも、ファイル形式は 3種類あります。
 
Accessのバージョンとファイル形式の関係と機能
https://dekiru.net/article/15674/
 
MDB ファイルが作成された Access のバージョンの取得
http://www.ruriplus.com/msaccess/tch/tch_041.html
 
 
Access2.0 は無いとしても、Access97 で作成した物は、2007以降は開けなかったはずです。
(2007が正しいか自信がありませんが。。)
 
2000で動いていたとの事なので、元が97形式であれば、
データベース形式を認識できないのも納得できます。
 
 
Access2000 があるのであれば、Excelで作成するのではなく、
Access2000にて新規mdbを作成、元のファイルから全てのオブジェクトをインポートすれば、
2000形式のmdbになります。
 
2000形式のmdbであれば、データベース形式云々のエラーは回避できると思います。
Excelでいちから作るとかよりは手っ取り早いので、試す価値は十分にあると思いますよ。

投稿日時: 18/12/05 16:59:42
投稿者: ビタミンVBA

Suzuさん、アドバイスありがとうございます。
 
できればAccessのない環境でも使いたいためにmdbファイルで作りたいと思っています。
(Access2007以降?で作ったAccdbでも開くだけならパッチを使う方法があるようですが、やってみたところうまく開けなかったので、結局mdbに戻りました)
私の理解不足ですが、mdb=Excel2003までの古い形式(Jetプロバイダ)で、Access2007以降は新しいACEエンジンで作るほうがいいのかと思ってExcel2010でもACEで作るのがいいのかと考えました。(今はExcel2010でもJetを使ってもいいと理解しています(間違い?)
 
今はExcel2010で作業していますが、当初はExccel2003で作ったmdbファイルをベースにいくたびか作り替えてきました。
(1)当初はExcel2003でmdb(Jet)を作成。
(2)Excel2010でも当初mdb(Jet)のまま使用していましたが、不安定(よくフリーズや応答なし)なので、ACEでmdbを作るのがいいのかとACEでmdbを作り変え。
不安定だったのは、Ecce2003とExcel2010を共存させていたことが主要因だったようで、Exceo2010のみにしましたが、mdbはACEのまま。ただJetでも作ったかもしれません。今回最適化をするまで、不具合があるとは思いませんでした。
 
前回、書きましたが、mdb(Jet)で作ったものはそのままAccess2000でも別PCのAccess2016でも開けますが、Excel2010で作ったmdb(ACE)はAccess2000では開くことができませんでした(以前は開けたのですが、最近はエラー。それでも読み書きは可能)
 
このため、改めてExcel2010でJetでmdbを作り直して見ることにしました。区切りのいいところで新規にmdbを作り、すべてのデータを再セットして動かしたら、一応同じ動作をすることが確認できました。
それで最適化を実行したら、問題なくできました(Access2000のまま。ADOで)。Access2016のPCでも最適化できました。
 
つまり、Excel2003でもExcel2010でもJetでmdbを作れば行けそうだと考えました(Jetが使えなくなることがなければ)。今回不具合と思ったのは、Excel2003/2010とJet/ACEとやっているうちにファイルを破損したためと思いました。
 
Access2007以降はJetからACEに移っているようですが、Excelに関してはJetのままでもいいのでしょうか。
(別問題ですが、Excel動作中によく応答なしになります。ただじっと待つとやがて回復します。この現象はOffice365(Exccel2016/Access2016)のPCのほうが多く、タスク終了で再起動が必要な場合もあります。)
 
とりあえず、問題が収まったようなので、しばらくしてから解決済みにさせていただきたいと思います。

トピックに返信