Agent Dagnamit


I'm running this code to allocate a random number to a field called [rnd100] to each row of a table containing around 350k rows.

Problem is, the code runs, but rapidly increases the size of the database to well over 1Gb (from under 100Mb).

What is going wrong

If I removed the row that allocates the random number, it runs fine. If I replace this with a line to simply update each row to a "1", the database still inflates.

Public Sub random100()

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT [rnd100] FROM [tblDATA] ", dbOpenDynaset)

rst.MoveFirst

While rst.EOF = False
rst.Edit
rst!rnd100 = Int((100 * Rnd) + 1)
rst.Update
rst.MoveNext
Wend

rst.Close
Set rst = Nothing

End Sub




Re: Why is my Access database becoming HUGE!!!

Shasur


You can try the Compact & Repair Database (Tools-->Database utilities-->Compact and Repair Database). This should reduce the DB Size

If you want to do the same using code, here is the way

http://vbadud.blogspot.com/2007/09/reducing-size-of-microsoft-access.html

Cheers

Shasur