Bloat Begone!
Oct 20, 2005
DESPERATELY SEEKING A BLOAT MASTER!
I'm working on an Access 2003 database with 2002-2003 Data Format. To avoid bloat, I decided to update data in a table using recordsets. Everyone thinks that queries are the devil of Bloat. Ha! Well I have to update 32,000 records. Everytime it updates a record, it adds 4000 bytes to the database size. I assume this is a Page Buffer. The record itself is probably at 500 bytes.
I don't mean to be picky, but for 32,000 records, that adds 128M Bytes to the database. If the user does this process 5 times that more, then it adds 640MB to the database.
IS THERE ANY WAY AROUND THIS?
View Replies
Sep 23, 2005
I just added a small number of code lines to the OnFormat in Detail section of two Reports and seem to have increased the db size by 300-500 kb. It wasn't anything big or tricky just making bold & coloured fonts in three or four fields in each report. Included is one extra report & the extra query on which the report is based. I don't usually watch the size of my db's but as this increase caught my eye I thought I'd ask if anyone has any comments on the subject. Besides inserting pictures what things will bloat a db?
View 3 Replies
View Related
Mar 16, 2015
I read a lot about Access database bloat when using temporary tables that reside in the front end, which was where I created and used temporary tables before reading about bloat.I therefore wrote a routine on startup that creates a temporary database and then copies the structure of the temporary tables into that temporary database using the DoCmd.TransferDatabase command. It kills the DB on exiting the application.I also read about keeping a persistent connection (handle) open to a linked DB as long as the main application is active, and this for efficiency reasons. I got this to work as well.
Being a self-taught Access programmer I have two questions:
1.Is the use of docmd.Transferdatabase a good way to work and does it reduce bloat by transferring the structure of the temp files to the temporary database?
2.If I want to work with the tables in my temporary database I use the following code for example:
Dim dbTemp As Database temporary database
Set dbTemp = DBEngine.Workspaces(0).OpenDatabase(strDBTemp)
Set rstRpt = dbTemp.OpenRecordset("tblRptPU001", dbOpenDynaset) ..
My question is this: if, at the end of the procedure, I write
dbTemp.close
set dbTemp = Nothing
does this leave the persistent connection open? The reason for the question is that I intend to split the database and soon go multi user, which is when efficiency is even more important than when working on a temporary database.
View 4 Replies
View Related
