Edit and Update

[Kevin McCartney]

Hi All,

I Have the following code, basically the code is to set the ranking number
against a set of records in a table called 'tblRANK'. The table contains
around 50,000 records and the ranking is across each country, in that the
ranking starts agian at 1 each time there is a new country, the table is
already sort by COUNTRY Asc and AMOUNT dec so the table is in the corret
order and the result is as expected.

This issue is, that the database size explodes massively and I don't
understand why and thus don't know of a solution on how to correct it. I've
tried BeginTrans and CommitTrans but that only seems to delay the increase in
the database size until the end. I'm expecting it to do something with that
I'm using simple DAO Edit and Update. Would I get the same issue if I try ADO?

Any help would be much appreciated.

TIA
KM


Dim wrkCurrent As DAO.Workspace
Dim dbCurrent As DAO.Database
Dim rstDATA As DAO.Recordset

Dim varReturn As Variant
Dim strCOUNTRY As String
Dim lngRECORD As Long

DAO.DBEngine.SetOption dbMaxLocksPerFile, 100000
Set wrkCurrent = DBEngine.Workspaces(0)
Set dbCurrent = CurrentDb
Set rstDATA = dbCurrent.OpenRecordset("tblRANK", dbOpenDynaset)

strCOUNTRY = rstDATA!COUNTRY
lngRECORD = 1

wrkCurrent.BeginTrans
Do Until rstDATA.EOF

If strCOUNTRY <> rstDATA!COUNTRY Then
strCOUNTRY = rstDATA!COUNTRY
lngRECORD = 1
End If

rstDATA.Edit
rstDATA!RANK = lngRECORD
rstDATA.Update
rstDATA.MoveNext

lngRECORD = lngRECORD + 1
Loop
wrkCurrent.CommitTrans

On Error Resume Next
rstDATA.Close
dbCurrent.Close
wrkCurrent.Close

Set rstDATA = Nothing
Set dbCurrent = Nothing
Set wrkCurrent = Nothing

 

[Clifford Bass]

Hi Kevin,

It probably does not make any difference ADO vs. DAO. The issue is
that you are updating a huge number of rows. Question: Do the numbers for a
particular entry really change with every run? If not, you can skip updating
those records that do not need to be changed.

If rstData!RANK <> lngRECORD Then
rstDATA.Edit
rstDATA!RANK = lngRECORD
rstDATA.Update
End If

Or, a possible alternative might be to put the rankings in a related
table. Could be worth trying; see if the bloat is lessened. It may be that
you will be stuck with having to do manual compact and repairs each time.