Reaches Maximum size (2GB) when calculating procedure

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")
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/11595-reaches-maximum-size-2gb-when-calculating.html
.Update
End With
rst2.MoveNext
Wend
rst2.Close
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11595
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

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

On Sat, 17 Jun 2006 09:48:22 +0200, "jj" wrote:

>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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11595
it, though, so this might not make any real difference (I suspect it
will run a lot faster though!)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11595

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]

You will find update queries to be superior in every way to VBA code loops
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11595
that do multi-file matches.

"jj" wrote in message
news:%23rVfpJekGHA.3484@TK2MSFTNGP03.phx.gbl...
> 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 <= " &
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11595
> 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
>