Database size constantly increasing

astraeuz
10-23-2004, 04:38 AM
I'm working with an application which does a massive amount of operations on my database. In the beginning, database size is atmost 1.3 MB but, it becomes more that 100 MB and some times ~500MB :-\
The application simply reads data, do some calculations and stores the updated data back to the DB.

I'm using following connection string to open the db connection

Set oCon = New ADODB.Connection
oCon.CursorLocation = adUseClient
oCon.Mode = adModeShareDenyNone
oCon.IsolationLevel = adXactBrowse
oCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db.mdb;Jet OLEDB:Database Password=pm$wd;"


oRs.Open "select * from table where bActive = true order by ID, UserID Desc", oCon, adOpenDynamic, adLockBatchOptimistic

While NOT oRS.EOF
......
......
oRS.Update
.......
.........
oRS.Update
........
.......
........
oRS.UpdateBatch
oRS.MoveNext
oRS.Close

Am I doing something wrong with Locks or the Connection Mode?

Shurik12
10-23-2004, 05:23 AM
Hi,

I'm afraid it's the nature of Access and you can't do much about it.
Just repair/compact your database on the regular basis (or better yet if it's feasible for
you, switch to MSDE)

Shurik.

astraeuz
10-23-2004, 05:46 AM
I'm doing the repair method to keep it running :).
Thanks.

rajeeshun
10-23-2004, 06:10 AM
Just one thing... I also had similar situation and one day my total .Mdb got corrupted and I cud not recover that...

MKoslof
10-23-2004, 09:40 AM
Unfortunately, Access will get sluggish if start dealing with a lot of records. If you are going to have a lot of records and you are dealing with sensitive data, you should look into MSDE or MySQL as a viable, affordable alternative.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum