Problem with MS Access - deleting records...

Hi,
  I have this app that interacts with an MS Access table in an mdb file. I added some 5k records and the size of the mdb file shows up as 300Kb.....then I deleted all the records.....still the size remanins the same!! I again added another 5K records and the size went up to 600Kb !! I again delete the records ...but the size remains the same!! Ne idea why this is happening?


Pssst...just try it out on ur system....am sure u'll face the same problem......just create a table manually.....add records...chk the size...delete the records and chk the size again!
manoj_joharAsked:
Who is Participating?
 
OnegaZhangConnect With a Mentor Commented:
http://www.kbalertz.com/kb_Q230501.aspx
Visual C++: Compacting an Access Database via ADO
1.      Besides #import, the Msado15.dll (MDAC2.1), add the following #import statement to generate the wrapper classes for JRO to your .cpp classes (alternatively, you can generate the wrapper classes more efficiently by using the no_implementation and implementation_only attributes of the #import pre-processor statement):

#import "C:\PROGRAM FILES\COMMON FILES\System\ado\MSJRO.DLL" no_namespace
                              

2.      Add the following (specifying your own source and destination database paths) to the .cpp file where you want to compact the database:

...
try
{
   IJetEnginePtr jet(__uuidof(JetEngine));
  jet->CompactDatabase( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\nwind2.mdb;Jet OLEDB:Database Password=test",
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\abbc.mdb;" \
"Jet OLEDB:Engine Type=4;Jet OLEDB:Database Password=test");
}
catch(_com_error &e)
{
   ::MessageBox(NULL, (LPCTSTR)e.Description( ), "", MB_OK) ;
}
                              

NOTE: The Jet OLEDB:Engine Type=4 is only for Jet 3.x format MDB files. If this value is left out, the database is automatically upgraded to the 4.0 version (Jet OLEDB:Engine Type=5). See the following table for appropriate values for Jet OLEDB:Engine Type:
Jet OLEDB:Engine Type      Jet x.x Format MDB Files
1      JET10
2      JET11
3      JET2X
4      JET3X
5      JET4X
0
 
OnegaZhangCommented:
you can include an empty mdb file into resource, when you feel it is time to pack the database, extract an empty database file, and export records into the new database to create a clean database.

welcome to www.fruitfruit.com
0
 
itsmeandnobodyelseCommented:
>>>> Ne idea why this is happening

That's very simple, MS ACCESS (and all other DBMS i know) don't rfree storage after delete. Normaly, that's an request directed to a file system but not to a DBMS.  

Regards, Alex
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
David MacDonaldChargé de projet processus d’affaires et systèmes TICommented:
There is a option in MSAccess to compact you database, as Onegazhang suggest ... but you can do it using the command line prompt

<path to>msaccess.exe <path to>yourbackend.mdb /compact


this should work. But be warned that it can be a lengthy process depending on the size of you mdb file, and lengthier if the databse is on the network.

regards, David
0
 
David MacDonaldChargé de projet processus d’affaires et systèmes TICommented:
The compacting option using MSAccess can be found under the tools->utilities->Compacting ...... (or something like that (i have a french system)) .
0
 
manoj_joharAuthor Commented:
Hi,
   Thanks all for those replies. Thing is that i am doing this 'programatically'. So even though i selected the compact database option I am just doing an OpenConnection using ADO Connection object.

 Neways, I figured that I can create a macro inside the mdb that will trigger the compact database command. Now I need to fire this macro from within my code. Any idea how to fire a macro from code?

0
 
David MacDonaldChargé de projet processus d’affaires et systèmes TICommented:
nice answer Onega ... i'll keep this one on my list of solution !

Regards!

David
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.