Reaches Maximum size (2GB) when calculating procedure

[jj]

Hi NG

I have two tables with approximately T1 = 1.5 million and T2 = 75000
records. I need to opdate T1 with information from T2, but my problem is
that it takes op very much space - and I dont know why - when I compact and
repair the database then it is reduced again, but I cant do that
automatically when running the procedure. Can anyone see through my problem.
?

My code is below:

Sub update_Jobnr
Set db = CurrentDb
Set rstCom = db.OpenRecordset("select Com from T2 group by Com")

While Not rstCom .EOF
Set rst = db.OpenRecordset("Select * from T2 where Com = " &
rstCom.Fields("Com") & "")
Set rst1 = db.OpenRecordset("select * from T1 WHERE Com= " &
rst.Fields("Com") , dbOpenDynaset)

While Not rst.EOF
If Not IsNull(rst.Fields("kode")) Then
If rst.Fields("side").Value = "U" Then
rst1.Filter = "Com= " & rst.Fields("Com") & " and KODE = " &
rst.Fields("kode") & " and no >= " & rst.Fields("From") & " And no <= " &
rst.Fields("To") & " and (no mod 2 = 1)"

Set rst2 = rst1.OpenRecordset()

While Not rst2.EOF
With rst2
.Edit
![Jobnr] = rst.Fields("Job")
.Update
End With
rst2.MoveNext
Wend
rst2.Close
Else
'The same code just with (no mod 2 = 0)
end if

End If
rst.MoveNext
Wend
rst.Close

rstCom.MoveNext
Wend
rstCom.Close

end sub

TIA
JJ

 

[John Vinson]

I have two tables with approximately T1 = 1.5 million and T2 = 75000
records. I need to opdate T1 with information from T2

Do you get the same bloating with a simple Update query, rather than
opening the tables as recordsets and using the Edit method?

Access isn't particularly good at garbage collection any way you do
it, though, so this might not make any real difference (I suspect it
will run a lot faster though!)

I'm not clear exactly what the purpose of this (frequent?) update
might be; is it really necessary to store this data, or could it be
calculated on the fly? Or is that just too slow given the size?

John W. Vinson[MVP]

 

[Pat Hartman\(MVP\)]

You will find update queries to be superior in every way to VBA code loops
that do multi-file matches.