I am curious to see if it is possible to compress an Access Database file from within IWB? Would I need to close my access to the file?
Thanks,
Bill
Yes, it is!
Found 3 methods on Google:
sub CompactMdb(string path)
istring newpath[260] = path
int iHresult
int index = len(newpath)-1
newpath[index] = `1`
DELETEFILE newpath
' 1
IDispatch access = CreateComObject("Access.Application")
if (access)
iHresult = access.DBEngine.CompactDatabase(path, newpath)
access->Release()
' TODO: replace file path with newpath, if newpath exisis and iHresult is zero
endif
' 2. DAO engine at least 3.5
newpath[index] = `2`
DELETEFILE newpath
IDispatch engine = CreateComObject("DAO.DBEngine.36") ' adjust version number
if (engine)
iHresult = engine.CompactDatabase(path, newpath)
engine->Release()
' TODO: replace file path with newpath, if newpath exisis and iHresult is zero
endif
' 3. JRO, MDAC must be at least 2.1
newpath[index] = `3`
DELETEFILE newpath
engine = CreateComObject("JRO.JetEngine")
if (engine)
iHresult = engine.CompactDatabase(APPEND$("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=", path), _
APPEND$("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=", newpath))
engine->Release()
' TODO: replace file path with newpath, if newpath exisis and iHresult is zero
endif
endsub
In the TODO part, the following code should be:
if (iHresult>=0 and PathFileExistsA(newpath) and DELETEFILE(path)) ' api
MoveFileA(newpath, path) ' api
return
endif
PathFileExists checks if a file exists, defined in shlwapi.inc
MoveFileA renames a file, use shlwapi.inc, windowssdk.inc, or windows.inc (_MoveFile)
More complete code:
$include "shlwapi.inc"
'CompactMdb("xyz\\dnsserv.mdb")
' returns zero on success
sub CompactMdb(string path),HRESULT
istring newpath[MAX_PATH] = path
HRESULT iHresult = E_FAIL
IDispatch engine
newpath[len(newpath)-1] = `1`
' 1. DAO engine at least 3.5
DELETEFILE newpath
engine = CreateComObject("DAO.DBEngine.120")
if (!engine) then engine = CreateComObject("DAO.DBEngine.36")
if (!engine) then engine = CreateComObject("DAO.DBEngine.35")
if (engine)
iHresult = engine.CompactDatabase(path, newpath)
engine->Release()
if (CompactMdb_Replace(iHresult, path, newpath)) then return 0
endif
' 2. JRO, MDAC must be at least 2.1
DELETEFILE newpath
engine = CreateComObject("JRO.JetEngine")
if (engine)
iHresult = engine.CompactDatabase(APPEND$("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=", path), _
APPEND$("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=", newpath))
engine->Release()
if (CompactMdb_Replace(iHresult, path, newpath)) then return 0
endif
' 3. use Access
DELETEFILE newpath
engine = CreateComObject("Access.Application")
if (engine)
iHresult = engine.DBEngine.CompactDatabase(path, newpath)
engine->Release()
if (CompactMdb_Replace(iHresult, path, newpath)) then return 0
endif
return iHresult ? iHresult : E_FAIL
endsub
sub CompactMdb_Replace(int iHresult, string path, string newpath),BOOL
int success = FALSE
if (iHresult>=0 and PathFileExistsA(newpath))
if (PathFileExistsA(path))
SHFILEOPSTRUCT SHop=0
SHop.wFunc = FO_DELETE
SHop.pFrom = path
SHop.fFlags = FOF_NOCONFIRMATION | FOF_ALLOWUNDO
if (SHFileOperation(&SHop)) then return FALSE
endif
success = MoveFileA(newpath, path)
endif
return success
endsub
Thanks for the code, Sapero. I did actually look it up via Google, but what I came up with was for VB and like the code you provided, way beyond my capabilities. There are commands in there that I don't understand.
I think I follow the 'if (!error)', the ! replaces NOT (at least I hope it does).
I do not understand the 'access->Release()', but will figure it out eventually.
I am guessing that IDispatch and HRESULT is actually a definition, like STRING? They probably exist in "shlwapi.inc".
I am planning on working on learning how to follow all this.
Bottom line, the code for compressing an Access Database was beyond my capabilities, but I can use your code to get it to work.
So Thank You for the code, I will add it into my program.
Bill