Using CompactDatabase function with AppWizard's database application

• Download demo project - 16.3 Kb
• Download source - 27 Kb

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:

1. Close all database instances
2. Call CompactDatabase member function
3. 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;    //You can use dynamic variable if prefer
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!