Solved

Problem with MS Access - deleting records...

Posted on 2004-10-14
7
228 Views
Last Modified: 2010-04-01
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!
0
Comment
Question by:manoj_johar
7 Comments
 
LVL 12

Expert Comment

by:OnegaZhang
ID: 12316907
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
 
LVL 39

Expert Comment

by:itsmeandnobodyelse
ID: 12319501
>>>> 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
 
LVL 1

Expert Comment

by:David MacDonald
ID: 12319864
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 1

Expert Comment

by:David MacDonald
ID: 12319885
The compacting option using MSAccess can be found under the tools->utilities->Compacting ...... (or something like that (i have a french system)) .
0
 

Author Comment

by:manoj_johar
ID: 12356029
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
 
LVL 12

Accepted Solution

by:
OnegaZhang earned 250 total points
ID: 12356336
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
 
LVL 1

Expert Comment

by:David MacDonald
ID: 12357062
nice answer Onega ... i'll keep this one on my list of solution !

Regards!

David
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Often, when implementing a feature, you won't know how certain events should be handled at the point where they occur and you'd rather defer to the user of your function or class. For example, a XML parser will extract a tag from the source code, wh…
Many modern programming languages support the concept of a property -- a class member that combines characteristics of both a data member and a method.  These are sometimes called "smart fields" because you can add logic that is applied automaticall…
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

820 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