Solved

CompactDatabase() & Vast Deletion of Records

Posted on 1998-04-17
6
362 Views
Last Modified: 2013-11-20
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?
0
Comment
Question by:sllsgl
  • 3
  • 3
6 Comments
 

Accepted Solution

by:
patel4694 earned 30 total points
Comment Utility
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.

0
 

Author Comment

by:sllsgl
Comment Utility
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.

0
 

Expert Comment

by:patel4694
Comment Utility
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 :)

0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:sllsgl
Comment Utility
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.
0
 

Expert Comment

by:patel4694
Comment Utility
Thanks deeply for your prompt help :)
0
 

Author Comment

by:sllsgl
Comment Utility
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 :)
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction: Displaying information on the statusbar.   Continuing from the third article about sudoku.   Open the project in visual studio. Status bar – let’s display the timestamp there.  We need to get the timestamp from the document s…
Introduction: Dialogs (2) modeless dialog and a worker thread.  Handling data shared between threads.  Recursive functions. Continuing from the tenth article about sudoku.   Last article we worked with a modal dialog to help maintain informat…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
This video discusses moving either the default database or any database to a new volume.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now