asked on

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?

Thanks

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

omgang

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

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tim

ASKER

Hi Jim,

Will this work with a. accdb file or just an mdb file?

Tim

ASKER

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.DeleteFile("C:\Working.accdb")
objFSO.MoveFile "C:\backup.accdb" , "C:\Working.accdb"

Open in new window