?
Solved

Problem with MS Access - deleting records...

Posted on 2004-10-14
7
Medium Priority
?
236 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 750 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction This article is the first in a series of articles about the C/C++ Visual Studio Express debugger.  It provides a quick start guide in using the debugger. Part 2 focuses on additional topics in breakpoints.  Lastly, Part 3 focuses on th…
  Included as part of the C++ Standard Template Library (STL) is a collection of generic containers. Each of these containers serves a different purpose and has different pros and cons. It is often difficult to decide which container to use and …
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

771 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