Hi All,

I have an AccessXP database that I use to produce a month end report.
I've used this db for the past 3 years without any issue until this
November month end run. It's a split db and when I tried to run it for
the November report the back end bloated up to the 2gig limit. I found
which table bloated and moved that table to a seperate back end,
relinking it to my front end and reran the process and the back end db
with just that one table bloated up to the 2gig limit and I don't
understand try now, I'm just looping thru the records and updating one
field.

the db with the one table in it starts with 623977 records and is
98.5megs at that point
I run the following vba code to update one field in that table, this
code has run for the past 3 years without issue

Set MyRec = MyDB.OpenRecordset("tblNewFeatures", dbOpenDynaset)
MyRec.MoveLast
MyRec.MoveFirst
Me!bxCounter.Visible = True
Me!bxCounter = 0
Do Until MyRec.EOF
MyRec.Edit
If MyRec!clm_type = "b" Then
MyRec!RptingCat = "Commercial Auto"
GoTo DidIt4
ElseIf MyRec!clm_type = "p" And MyRec!PolSym = "h" Then
MyRec!RptingCat = "Personal Property"
GoTo DidIt4
ElseIf MyRec!clm_type = "p" And MyRec!PolSym = "f" Then
MyRec!RptingCat = "Personal Property"
GoTo DidIt4
ElseIf MyRec!clm_type = "w" Then
MyRec!RptingCat = "WC"
GoTo DidIt4
End If
DidIt4:
MyRec.Update
MyRec.MoveNext
Me!bxCounter = Me!bxCounter + 1
DoEvents
Loop

I just don't understand how a AccessXP backend db of 98.5meg size can
grow to its limit of 2gig when I'm just looping thru the records and
updating one field as in the above code
Anyone have any idea's what's happening or how I can run this?
thanks
bobh.

"bobh" <vulcaned (AT) yahoo (DOT) com> wrote

I don't know what is causing the bloat, but the following query should do
the same thing without bloating the BE

UPDATE tblNewFeatures SET tblNewFeatures.RptingCat = IIf([clm_type]="P" And
([PolSym]="h" Or [PolSym]="f"),"Personal
Property",IIf([clm_type]="W","WC",IIf([clm_type]="B","Commercial
Auto",[RptingCat])))
WHERE (((tblNewFeatures.clm_type)="p") AND ((tblNewFeatures.PolSym)="h")) OR
(((tblNewFeatures.clm_type)="p") AND ((tblNewFeatures.PolSym)="f")) OR
(((tblNewFeatures.clm_type)="w")) OR (((tblNewFeatures.clm_type)="b"));

Have you regularly been doing Compact and Repair on the back end database?
The way Access works internaly, no-longer-used disk space (which may result
from deleted records, or internal work space, or ... ) is not actually
removed until a Compact and Repair is performed. But, once you get to or
near the 2 GB size, you may need very substantial free disk space on your
machine for the Compact and Repair to work.

In Access 2002 (the Access version in Office XP, often called Access XP),
open Access without opening a database, on the Menu, click Tools, in the
Dropdown, click Database Utilities, then Compact and Repair Database, and
follow the prompts. Just for safety's sake, I do not compact a database
back into a file of the same name -- some of my colleagues may say that is
an unnecessary caution, but following that practice, I've never had a
database end up corrupted while doing a Compact and Repair. (Though I have
had some already-corrupted databases that Compact and Repair could not
repair.)

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access


"bobh" <vulcaned (AT) yahoo (DOT) com> wrote

There may be some corruption in the table that is bloating.
So I would create a new empty table and import all the records from the
existing table into the new table. Then I would add in any indexes, etc.

I agree that this would best be done using a query to update the records. The
query will almost always be faster then stepping through the records one at a
time.

If you really need to use the VBA routine, I would suggest you change it to
drop some of the unneeded code and use a query to update only the records that
need to be updated.

Dim strSQL as String
'only update the records that need to be updated
'by selecting only those you will update
'You might need to expand the where clause to check if there
'is a valid value or not in rptingcat.
strSQL = "SELECT clm_Type, polSym, RptingCat FROM tblNewFeatures" & _
" WHERE (clm_Type = 'p' and PolSym in('h','f')) " & _
" OR (clm_type in ('w','b')"

Set MyRec = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
'MyRec.MoveLast 'not needed and just wastes time
'the only reason to MoveLast in this situtation would be
'to get an accurate record count for records in the recordset
'and I don't see where you get or use such a count
'MyRec.MoveFirst 'not needed
Me!bxCounter.Visible = True
Me!bxCounter = 0
Do Until MyRec.EOF
MyRec.Edit
If MyRec!clm_type = "b" Then
MyRec!RptingCat = "Commercial Auto"
'GoTo DidIt4 'not needed. IF clm_type = "b" then all
'the remaining tests (elseIf) are skipped and the code moves to
'the end if.
ElseIf MyRec!clm_type = "p" And MyRec!PolSym = "h" Then
MyRec!RptingCat = "Personal Property"
'GoTo DidIt4 'not needed
ElseIf MyRec!clm_type = "p" And MyRec!PolSym = "f" Then
MyRec!RptingCat = "Personal Property"
'GoTo DidIt4 'not needed
ElseIf MyRec!clm_type = "w" Then
MyRec!RptingCat = "WC"
'GoTo DidIt4 'not needed
End If
'DidIt4: 'not needed
MyRec.Update
MyRec.MoveNext
Me!bxCounter = Me!bxCounter + 1
DoEvents
Loop


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On 12/7/2010 4:37 PM, bobh wrote: