Access Database "Compress/Repair"

[Dirk]

How can I envoke Access's "Compress/Repair Database" from VB.

I want to be able to store the database on a 3-1/4 inch disk but, I find the database file will grow too larger after records have been deleted and added.
Access 's "Compress/Repair Database" reduces the size to fit on the disk. I would run this just before I need to save it.

TIA

[MartinLiss]

You can also just do Compact, but this is an example of doing both.

                    DBEngine.RepairDatabase sDBName
                    
                    DBEngine.CompactDatabase sDBName, "compact.bak"
                    Kill sDBName
                    Name "compact.bak" As sDBName

[Edited by MartinLiss on 05-19-2000 at 02:41 PM]

[Dirk]

I can not get this to work. It says it doesn't reconise my database format. This command only seems good for DAO.I am using ADO to connect to my database. Is there an equvilant ADO command?

Thank Again

Hello,

I don't know about ADO but I do know that you must close the database before you try to compact it.

In the MSDN Library I did find this though:



MDAC 2.5 SDK - OLE DB Providers OLE DB Provider for Microsoft Jet


Compacting Jet Databases
Previously, there was no way through the Jet 3.5 OLE DB Provider to compact Jet databases. There is also no official mechanism in OLE DB to do maintenance work on databases at this time. The 4.0 provider now exposes a provider-specific interface called IJetCompact that can be used to compact databases and manage compaction-related properties, such as database encryption and database repair.

Copywrite MSDN April 2000

Hope this helps

[MartinLiss]

There is probably an ADO equivalent, but I don't do ADO so I can't help.

I don't know what RvA's MSDN article is trying to say, because I have been successfully compacting and repairing Jet databases for years with the code I supplied you with.

MartinLiss,

Yes I fully agree with you, your code will work just fine in DOA,
the artical I found is directed at ADO Not at DOA.

That was the only referance I could find in the library that didn't deal with DOA. Just trying to give a little help

Best,

Since Access 97 can't be compacted using ADO,
I'm using DAO DBEngine.CompactDatabase even though I'm using ADO through out the program, just you have to disconnect from the dbfile before compacting and reconnect afterwords.

[Thom]

I just wanted to add a BIG THANKS ya'll!! to this thread! This stuff is EXACTLY what I needed to get my project near completion-I had to be able to repair any damage & compact my DB after my users get their hands on it <g>(using A2K with VB 6.0 interface).

I'm still new at this, so 1 question; Clunietp-how does the JRO procedure ensure that the Nwind DB is compacted/repaired if the source DB is now NewDB.mdb?? Is there a process to copy NewDB back to Nwind2k??

How does this help after a 'brilliant' user starts to save his test results to the DB & then turns off his PC by mistake?(thus corrupting the A2k DB I assume)? Will this procedure repair the DB to before the user damaged it? Well, anyway, I'll keep searching for those answers here. It's a pleasure to get professional help from this forum!

[MartinLiss]

Is there a process to copy NewDB back to Nwind2k??

Kill "Nwind2k.mdb"
Name "C:\NewDb.mdb" As "Nwind2k.mdb"

[Thom]

Doh!!! I should've caught that one-Thanks MartinLiss I >really< appreciate it!