May 24, 2022, 02:42:11 AM

News:

Own IWBasic 2.x ? -----> Get your free upgrade to 3.x now.........


Compress Access Database

Started by billhsln, April 27, 2011, 06:25:18 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

billhsln

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
When all else fails, get a bigger hammer.

sapero

April 27, 2011, 11:37:14 PM #1 Last Edit: April 28, 2011, 01:14:21 AM by sapero
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

billhsln

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
When all else fails, get a bigger hammer.