Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Reaches Maximum size (2GB) when calculating procedure

Discussion in 'Information Technology' started by jj, Jul 28, 2006.

  1. jj

    jj
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    USA Sikh Car Salesman Reaches Settlement With Lexus Dealership Breaking News Nov 30, 2013
    Canada Sikh Community reaches out to serve in aftermath of Calgary Floods Breaking News Jun 28, 2013
    Opinion London Marathon 2012: Paralysed Claire Lomas Reaches Finishing Line After 16 Days Breaking News May 15, 2012
    India Infant Deaths Continue in Bengal Hospitals, Toll Reaches 37 in 5 Days Breaking News Nov 3, 2011
    United Sikhs United Sikhs Aid Reaches Japan Sikh Organisations Mar 16, 2011

  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Sat, 17 Jun 2006 09:48:22 +0200, "jj" <jj@get2net.dk> 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
    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]
     
  4. Pat Hartman\(MVP\)

    Pat Hartman\(MVP\)
    Expand Collapse
    Guest

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

    "jj" <jj@get2net.dk> 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 <= " &
    > 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
    >
     

Share This Page