CompactDatabase() & Vast Deletion of Records

Here is a simplae function that calls Win32 API function Copy File that will replace you original database with the compacted one, then reopen all the databases, recordset, workspace objects.
Remember that compact database will fail if and DAO objects like the CDaoRecordset or CDaoDatabase objects are open, since the funtion requires exclusive rights to the file.

BOOL CDataSwap::ReplaceFile()
{
      // file name, destination and source
      CString TargetFile = "c:\\compacted.mdb";
      CString DestFile = "c:\\database.mdb";

      ... Close all Recordsets, Databases, Workspaces that reference this databases file

      //  Do the copy, or return false if unsuccessful
      //  The third param, is FALSE - meaning replace the file
      //  is it exists, be sure to create a backup of the file
      //  before doing this, incase something goes wrong
      if (!CopyFile(TargetFile, DestFile, FALSE))
            return FALSE;

      // We made it!  Successful Copy!  Let's get out of here!
      return TRUE;
}


As for the SQL, yes you can write different SQL statements for you recordset object, just be sure the returned fields are the same for all SQL used by that recordset object. eg.

if you table in the database has four fields: CarsID, Name, BeginProduction, and EndProduction

then if you recordset object in VC has only two fields:

class CSetCars:CDaoRecordset
{
...

// fields
long m_CarsID;
CString m_Name;

...
}


you can open that recordset object using as many SQL that you can create, so long as each of these SQL's returns the three feild, and only those three fields.

so here are a few possible SQLS

"SELECT Cars.CarsID, Cars.Name FROM Cars;"
- returns all car Id's and names in the table

"PARAMETERS pStartDate DateTime, pEndDate DateTime;
SELECT Cars.CarID, Cars.Name
FROM Cars
WHERE (((Cars.BeginProduction)>=[pStartDate]) AND ((Cars.EndProduction)<=[pEndDate]));
- returns those car ID's and Names that fall into the date range specified.


(tip, use Access 95 or 97 to create these Queries. If you don't know how to you will have to just play arround with the wizard provided that help to create the queries etc and you can read help also)



As for the last thing, yes you can create multiple CDaoDatabase object for a single database file that you have.  There for if you have two recordset objects that use two different CDaoDatabase objects then you must close all four objects before you begin you Compact database routine.

Thanks Pate, One Last Question ?

How to Close ALL that are supposed to be closed???  Which of them ???
Basically CDaoRecordset object m_pSet, CDaoDatabase object m_pSet->m_pDatabase, CDaoWorkspace m_pSet->m_p???,  & how many more to close ???

Do I simply close with {OBJ Handle}.Close(); // with no param

Also, after Closing & Replacing, to open them again to the original state, how should I do it, the actual sequence & params needed ???

Pls help :)

Thanks again :)

if you created your app with the app wizard, then simply look in the CDocument class of your project, where the set is contructed.


The sequence of closing the all the objects is the opposite of when you open, or in your case reopen the object to their original state.

Lets say my project did not have any dao related code, and I wished to add dao to the project.  The following is what I  would have to do in order to succesffully create/open the objects(assuming I've already included the required MFC header files):

function()
{
1) CDaoWorkspace workspace();
   workspace.Open("workspace name");
   CDaoDatabase database(&workspace);
   database.Open(....);

      // or simply the following where a default workspace is
      // created for you if it is not provided.

   CDaoDatabase database();
   database.Open(....);

2) CDaoRecorset set(&database);
   set.Open("Query/Table Name or SQL");


...do stuff

3) set.Close();
   database.Close();
   workspace.Close();

... Compact the database

4) workspace.Open("...");
   database.Open(...);
   set.Open(...);
}



This will function will make sure the recordsin the set at the beginning are the same as the ones at the end of the function call.  However you may lose the record position you were on before the compact since the second open will reset the position in the recordset to the first record.  So, you may want to remmeber the records you were on by saving it's recordID temporarily.

Thanks deeply for your prompt help :)

Hi, I am back again with problems with RepairDatabase().  

I am already able to close all database objects (CDaoWorkspace, CDaoDatabase & CDaoRecordset); do CompactDatabase() & reopen all database objects back to its original states.

I tried to do a RepairDatabase() before CompactDatabase(), but with this extra step, the application will crash with a DaoCore Assertion, can anyone guide me on how to do it correctly?  Anyway, do I need a RepairDatabase() process, how can I know if the database is corrupted in VC++5 & it requires a RepairDatabase() operation ???

Thanks again for any help :)