Solved

Problem with MS Access - deleting records...

Posted on 2004-10-14
7
231 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 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

Technology Partners: 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

When writing generic code, using template meta-programming techniques, it is sometimes useful to know if a type is convertible to another type. A good example of when this might be is if you are writing diagnostic instrumentation for code to generat…
This article will show you some of the more useful Standard Template Library (STL) algorithms through the use of working examples.  You will learn about how these algorithms fit into the STL architecture, how they work with STL containers, and why t…
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.

739 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