Click here to Skip to main content
Click here to Skip to main content
Go to top

Using CompactDatabase function with AppWizard's database application

, 17 May 2004
Rate this:
Please Sign up or sign in to vote.
This article shows how to use CompactDatabase() function with database application created with AppWizard, and how to calculate compression ratio. Solved problems: closing database instances, and reopening database without being noticed by users.

Sample Image

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!

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Anton P. Ivanov
Researcher
Russian Federation Russian Federation
No Biography provided

Comments and Discussions

 
GeneralNo need for a CDaoWorkspace instance to call CompactDatabase() PinmemberSie Raybould6-Oct-07 4:07 
GeneralErrors reading the DB after recompact. PinmemberWREY18-May-04 11:23 
It would seem to me that trying to recompact a database would be the normal thing to do. (Isn't that the idea behind trying to keep the database as compact as possible?)
 
According to your sample, once compacting is done, if the user were to trying any further compacting of the database, then try to read from it, a series of errors and "assertions" would follow, including the pre-emptive closure of the program.
 
Also, because the sample does not allow the user to enter any new records, there was no way to test (or see) how effective the compacting of the database really works. However, just looking at the result from your sample, the database seems to be smaller when it was NOT compacted. If compacting a database makes it LARGER, why compact it in the first place?
 
Unsure | :~
 
William
 
Fortes in fide et opere!

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 18 May 2004
Article Copyright 2004 by Anton P. Ivanov
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid