Solved

Problem with MS Access - deleting records...

Posted on 2004-10-14
7
225 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Introduction This article is a continuation of the C/C++ Visual Studio Express debugger series. Part 1 provided a quick start guide in using the debugger. Part 2 focused on additional topics in breakpoints. As your assignments become a little more …
Container Orchestration platforms empower organizations to scale their apps at an exceptional rate. This is the reason numerous innovation-driven companies are moving apps to an appropriated datacenter wide platform that empowers them to scale at a …
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 learn how to clear a vector as well as how to detect empty vectors in C++.

786 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