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.
"Kevin McCartney" wrote:
[Quoted Text] > 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
|