Compact access database

  • Thread starter Thread starter gatarossi
  • Start date Start date
G

gatarossi

Dear all,

This code below compacts the access database, but is it possible to do
it in vba code from excel?

Sub compact_bd()
Dim strDBtoBackUp As String
strDBtoBackUp = ThisWorkbook.Path & "\bd.mdb"
DBEngine.CompactDatabase strDBtoBackUp
End Sub

Thanks a lot!!!

André.
 
Hi Andre

AFAIK neither Excel nor VBA has any engine to compact a database. So what
your code should do is to start an Access instance and remote control it to
do the work. See http://www.erlandsendata.no/english/index.php?t=envbaole on
how to get started on this technique.

HTH. Best wishes Harald


Dear all,

This code below compacts the access database, but is it possible to do
it in vba code from excel?

Sub compact_bd()
Dim strDBtoBackUp As String
strDBtoBackUp = ThisWorkbook.Path & "\bd.mdb"
DBEngine.CompactDatabase strDBtoBackUp
End Sub

Thanks a lot!!!

André.
 
Dear all,

This code below compacts the access database, but is it possible to do
it in vba code from excel?

Sub compact_bd()
Dim strDBtoBackUp As String
strDBtoBackUp = ThisWorkbook.Path & "\bd.mdb"
DBEngine.CompactDatabase strDBtoBackUp
End Sub

Thanks a lot!!!

André.
Click to expand...

Create a reference in the Excel workbook to the appropriate DAO
object. In Excel 2007, it would be "Microsoft Office 12.0 Access
Database Engine Object Library"

In Excel 2003, I think it is DAO 3.5 or DAO 4. Can't recall off
the top of my head.

Then, the following code will do it.

Sub CompactAccessDatabase()
Dim szSourceDatabase As String
Dim szDestinationDatabase As String

szSourceDatabase = "C:\Filename.accdb"
szDestinationDatabase = "C:\Compacted.accdb"

DBEngine.CompactDatabase szSourceDatabase, szDestinationDatabase

End Sub

You might be able to play with it and get it to use the source
and destination as the same so it compacts over itself like
Access does when you manually compact without having to create a
destination database as I have done above. If not, just a few
more lines of code to delete the source and rename the
destination with the source name.
--
__________________________________________________________________________________
Ed Hansberry (Please do *NOT* email me. Post here for the benefit of all)
What is on my Pocket PC? http://www.ehansberry.com/
Microsoft MVP - Mobile Devices www.pocketpc.com
What is an MVP? -
 
Back
Top