Compacting Access Database using DAO

Hey guys, So I am trying to compact an Access Database using VBScript and DAO. I have it working on a workstation with Access 2010 installed on it, but if I try to use it on a server 2012 with just the Access 2010 runtime it errors out Creating ActiveX Component for Access.Application. From what I have read the runtime by design doesnt do this,

Are there any workarounds for this?


Const CreateLog = True
Set objAccess = CreateObject("Access.Application")
Set objFSO = CreateObject("Scripting.FileSystemObject")
errReturn = objAccess.CompactRepair("C:\Working.accdb", "C:\backup.accdb", CreateLog)

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

omgangIT ManagerCommented:
I'm guessing you've already considered this but installing a full version of Access onto the server is a workaround for your specific problem.

Is the Access file on the server a back-end data file that you wish to compact periodically?  Another possible workaround is to copy the file to the workstation with the full version of Access, compact it there, and then copy it back.  You could rename the existing copy on the server before pasting the compacted copy as a protective measure.  You should be able to do all of that via vb script.

OM Gang
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Actually, you're not calling DAO, your using Access.

For DAO directly, it would be something like this:

Set objEngine = WScript.CreateObject("DAO.DBEngine.36")
objEngine.CompactDatabase "C:\myDB.MDB", "C:\Temp.DB"
If Err = 0 Then
' delete DB
' Copy temp to DB
' Delete temp.
  ' error in compact.
End If


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TimAuthor Commented:
Hi Jim,

Will this work with a. accdb file or just an mdb file?
TimAuthor Commented:
final code:

Set objAccess = CreateObject("DAO.DBEngine.120")
Set objFSO = CreateObject("Scripting.FileSystemObject")
errReturn = objAccess.CompactDatabase("C:\Working.accdb", "C:\backup.accdb", NULL, dbVersion120, NULL)
objFSO.MoveFile "C:\backup.accdb" , "C:\Working.accdb"

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.