Database Bloating

[Margie]

In Access 2003, my database "bloats" when using a recordset to update fields.
In reading Microsoft article #289562, it suggests explicitly closing the
recordset when done to prevent this, but my problem happens within one module
(I work with larger tables). The same module runs fine in Access 97, so I'm
just encountering this once I converted to Access 2003. In this instance, my
mdb file starts at 666,536kb, I run the following code, and during execution
I get an error "Invalid Argument" on the rst.Update command, and my database
has grown to 2,097,152kb. Any thoughts?


Dim db As Database
Dim rstMain As Recordset

Set db = CurrentDb

Set rstMain = db.OpenRecordset("tblMain")
DoCmd.HOURGLASS True

rstMain.MoveFirst
While Not rstMain.EOF
rstMain.Edit
rstMain![OCCUPANCY] = "U"
rstMain![PROPERTY TYPE] = "U"
rstMain.Update
rstMain.MoveNext
Wend
rstMain.Close
DoCmd.HOURGLASS False

 

[John Spencer]

Instead of doing this the hard way use an update query. It will be a lot
faster and will not cause unnecessary bloat.

Dim db As DAO.Database
Dim strSQL as String

Set db = CurrentDb()
strSQL = "UPDATE tblMain Set Occupancy =""U"", [Property Type] = ""U"" "

db.Execute strSQL, dbFailOnError
DoCmd.HOURGLASS True
Set rstMain = db.OpenRecordset("tblMain")
DoCmd.HOURGLASS False

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..