CompactDatabase() & Vast Deletion of Records

I have a new question regarding CompactDatabase(), how can I use it to
compact my already active database?  The Close() function is not enough
to "log off" from the database, & CompactDatabase() still complains that
I am unable to proceed with the operation.  Pls teach me how to make it
work?  I must make sure that the compacted database will be replacing
the original set.

I will be using CompactDatabase() as a tool to "organise" my database
(smaller size), produce a Backup Copy & Restore a Backup copy. Is what I
am going to do possible with CompactDatabase?

Another question is regarding vast deletion of old records from the
database.  I am currently doing deletion of records individually from
the first record of the database.  It seems inefficient, is there a
better way like executing some SQL statement?  Pls guide on how to pass
in the SQL statement since this time, the SQL statement will be acting
on the main m_pSet.  (I noticed that once I Close() the m_pSet,
reopening it doesn't seems to work anymore ??? Purely Open() & Close()
with no params, am I right?)

Lastly, is it possible to change the name of the connected database
while the program is running?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

How many CDaoDatabase objects do you construct in your app?  You may want to make sure that all Databases and Workspace objects are closed to ensure there is no open connection to the database via a recordset/querydef/ or tabledef object.

As for the deletion of the records from the database, the best way is to open the database in access and create a Query that does what you need it to do, like " DELETE DISTINCTROW set.* FROM set WHERE .....;" and save the query in the database.  Then Just construct and open a CDaoDatabase object in your app, and call the Execute() member function passing the name of your stored query.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sllsglAuthor Commented:
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
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 :)

Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

sllsglAuthor Commented:
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):

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

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

   CDaoDatabase database();

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

3) set.Close();

... Compact the database

4) workspace.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 :)
sllsglAuthor Commented:
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 :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.