Introduction
There are many articles about CompactDatabase()
method on the web. But, I couldn't find any simple descriptions of using it. You probably know, when using CompactDatabase()
function (member of CDaoWorkspace
class), you need to close all instances of any CDaoDatabase
, CDaoRecordset
or CDaoWorkspace
classes used by MFC to open your database. If users have a little experience (like for me, when I started using DAO), this actions would be difficult, especially because it is hard to find any information about it. This article will help you to understand some confusion around of CompactDatabase()
function.
Some notes for beginning - you must use MFC single document application, document/view support and DAO compatible database with file support. Use AppWizard to create an application like this.
You need 3 steps to do this:
- Close all database instances
- Call
CompactDatabase
member function
- and reopen database (like Microsoft Access does)
Also, in this article, I show you how to calculate compression ratio.
Close database instances
The greatest trouble is given by incorrect closing of database instances. Many people try to close the database using function Close()
, a member one of CDaoRecordset
, CDaoTableDef
or CDaoDatabase
classes. To do this, they create a new variable of one of these classes (static or dynamic variable), and then call Close()
function. For example, wrong code looks like this:
...
CDaoDatabase *mydb = new CDaoDatabase();
mydb->Close();
...
delete mydb;
...
This code gives an exception error: you can't call Close()
function without opening the database.
If you have created your application with AppWizard, and included database with file support (on application creation step), you must have foreign variable m_pSet
that links to CDaoRecordset
class (for example, CMyRSet
). In your view class (for example, CMyRView
), add message map function for menu or button OnCompactDatabase()
or something like this. In my application, I have menu item "Compact Database" and message map function void OnCompactDatabase()
.
Now, add next line to OnCompactDatabase()
:
...
m_pSet->m_pDatabase->Close();
...
m_pDatabase
variable that points to CDaoDatabase
base class is the parent class for your database application.
Call CompactDatabase()
All you need to call this function is to determine variable for CDaoWorkspace
class and directly call CompactDatabase()
member function. Add these lines to your code (to OnCompactDatabase()
message map function):
...
CDaoWorkspace cdw;
cdw.CompactDatabase("source.mdb", "dest.mdb");
...
Note: look for more information about CompactDatabase
in MSDN.
Reopen database
And last step consists of reopening the database. As with Close()
function, many people use CDaoDatabase::Open(...)
function, but this method is very complicated and confused because you need to also manually reopen your recordset and do some others steps not described in this article. Such approach raises many exception errors. My approach is much more simpler.
Database and recordset is initialized by CRecordView
class (ex., CMyRView
) in OnInitialUpdate()
member function. All you need to reopen the database is recall this member function from OnCompactDatabase()
member function.
How to calculate compression ratio
Create variable CFile file
and use the code below:
CFileStatus filestat;
CString sNorm, sComp, sRation, msg;
int nSourceLen, nDestLen;
...
file.GetStatus("source.mdb", filestat);
nSourceLen = filestat.m_size;
sNorm.Format(_T("Normal Size: \t%d kbytes\n"), filestat.m_size / 1024);
file.GetStatus("dest.mdb", filestat);
nDestLen = filestat.m_size;
sComp.Format(_T("Compact Size: \t%d kbytes\n\n"), filestat.m_size / 1024);
sRatio.Format("Compression: \t%d %%", nDestLen * 100 / nSourceLen);
msg = sNorm;
msg+= sComp;
msg+= sRatio;
MessageBox(msg, "Results ...", MB_OK | MB_ICONINFORMATION);
...
Summary
For your satisfaction, I'm including the code you will need. Your OnCompactDatabase()
should look like this:
void CMdbView::OnDatabaseCompact()
{
CFile file;
CFileStatus filestat;
CString sNorm, sComp, sRatio, msg;
int nSourceLen, nDestLen;
CDaoWorkspace cdw;
m_pSet->m_pDatabase->Close();
cdw.CompactDatabase("source.mdb", "dest.mdb");
file.GetStatus("source.mdb", filestat);
nSourceLen = filestat.m_size;
sNorm.Format(_T("Normal Size: \t%d kbytes\n"), filestat.m_size / 1024);
file.GetStatus("dest.mdb", filestat);
nDestLen = filestat.m_size;
sComp.Format(_T("Compact Size: \t%d kbytes\n\n"), filestat.m_size / 1024);
sRatio.Format("Compression: \t%d %%", nDestLen * 100 / nSourceLen);
msg = sNorm;
msg+= sComp;
msg+= sRatio;
MessageBox(msg, "Results ...", MB_OK | MB_ICONINFORMATION);
OnInitialUpdate();
}
Now, users need not reload the program after packing a database. They even will not notice the process of database packing. Very simple, isn't it?
Enjoy!
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.