Link to home
Start Free TrialLog in
Avatar of ankursinha
ankursinha

asked on

MS Access Database gets Bloated in VB Application ATTN:V.URGENT REPLY ASAP

The application is a toll management program built in VB6.The database which stores the transactions
gets bloated in size very frequently.For e.g upto 1GB within a week.But when i export the tables into
another DB the size is only about 5MB.PLs help me out here.The program is run 24x7 so the volume is
very large.Is there any way this can be resolved by code
Avatar of Dirk Haest
Dirk Haest
Flag of Belgium image

'**********************************************************
'Passing values compactDB module
'Sour_path = Source path of existing database.
'Dest_Path = Target path
'**********************************************************

Option Explicit
 
Public Function compactDB(ByVal SOUR_path As String, _
   ByVal DEST_path As String) As Boolean
 
  On Error GoTo Err_compact
  Private JRO As New JRO.JetEngine

' Source and Destination connection path
  Private DB_sour As String, DB_dest As String

  DoEvents
  DB_sour = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
      & SOUR_path
  DB_dest = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
      & DEST_path & " ;Jet OLEDB:Engine Type=5"
 
     JRO.CompactDatabase DB_sour, DB_dest
 
  compactDB = True
  Exit Function
 
Err_compact:
  compactDB = False
  MsgBox Err.Description, vbExclamation
End Function

Previous post was to copy the database to a new destination and compact that one...

This is the only thing you have to do to compact your database:
DBEngine.CompactDatabase OriginalDB, CompactedDB
The biggest problem with Access is use of temp tables that it uses to perform any update, delete and insert operations. It unfortunately doesn't delete these tables and you need to either manually or programatically compact the database. This link will tell you how it is done programatically. I am assuming that ADO is being used with Access 2000 as backend:

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q230/5/01.ASP&NoWebContent=1

if link doesn't open then:

Dim jro As jro.JetEngine
Set jro = New jro.JetEngine
jro.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\mydatabase.mdb", _
"Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=4"

Avatar of Voodooman
Voodooman

Hi

Just a note.

The reason that your database gets big is that when you delete records the space is not freed up.  In actual fact the records are still there and are just marked deleted.

In the good old DOS days you had to 'Purge' (Compact) your database regularly to clear these records.  We could also Undelete them!

I guess ankursinha is deleting and replacing lots of records.

Voodooman
Using Access for a 'mission critical' (24x7 application) is a VERY VERY VERY bad decision.  Access MDBs become 'bloated' as explained by both Voodooman and Sethi.  And trying to 'compact' a database that is 'running' is ALMOST impossible, as the Compact action actually creates a COMPLETE new Database - a 'copy' fo the old database, into which ALL of the objects that have not been marked for deletion are copied, and your application would then need to connect to the NEW copy, on the fly.

AW
Avatar of ankursinha

ASKER

For all the solutions i've recieved i.e the compactdatabase method,i need exclusive access to the DB which is not possible in my case.Any Other Solutions please.ANd also,I can't switch to another DB engine.
Anyway,Thanks for ur Replies
You cant compact without getting exclusive access.
Hi

You need to look at yopur strategy here.

A couple of years ago I worked on a management information system that imported 22 million records a day from Cobal into SQLServer.

I was employed as a consultant DBA because the client was complaining of the performance of the server.  When I counted the recs I calculted 22 million a day - the SQLServer was doing a fantastic job.  I changed the strategy to only importing the last 7 days daily and this reduced the transactions to 10k a day.

Now you have only a few thousand recs (I estimate around 30 thousand) is it really necessary to delete and replace all of them all the time? I estimate that you are deleting the all 30k records 200 times a week - is this really necessary - I wouldnt think so - your strategy would seem to be inappropriate.

Couldnt you just add - say yesterdays transactions - its a toll system so you should be just adding recs - you would never have any need to change an old transaction - no one ever gave me back a toll anyway!


Voodooman
Hey Guys,Desperation Stage Here!!!
Voodoman:i am not deleting or replacing any record,its just addition of recs im talking about.We just add records
to the DB and its size bloats.

Further Help needed,If anyone can help me here ill award half points .
How can i copy records from one password protected MS Access DB to Another Password Protected Access DB
using Insert command
Note:The IN clause is supposed to work here but strangely it doesnt
If anyone can gimme working code for doing this using Insert(or any other method if Insert Not Possible)
It will be a great help.

THankx Guys

Hi

How many recs have you got!

1gb estimates to 6+ million (I had 6.2 million and that was about 950mb)  - are you sure about the size of your Database?


Voodooman
Yeah I am Sure!!!

Believe it or not,I had only 60 odd thousand records when the size was 1GB
But when I compact it or Export the tables with the data to a blank DB the size is barely 6-7 MB
And I don't even have any images or anything of that sort in my DB.There are only 12 fields
of textual data(includes Dates,numbers etc.)

Help Me If U Can PLEASE

AND Also Can Anyone help me out In my other problem also.
ASKER CERTIFIED SOLUTION
Avatar of Sethi
Sethi
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial