Access stoped responding - SQL problem

[MrDeej]

Hello

I have noticed that access usually "stops responding" when i activiate large SQL operations.
This is a problem in the case that i want i. e. a postcounter which tells the user how many post that are left in the update process.

I usally use this type of code.

Dim RST as NEW ADODB.Recordset

RST.open sometable, , CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    do untill RST.eof
    with RST
                (do something, maybe a dlookup feature)
     i = i +1
     me.etk_information.caption = "Working on post " & i & " of a total " & totalpost & " posts"
me.repaint
loop
RST.close
Set RST = nothing

The SQL code does exactly what it supposed to, except that access "stops responding" after a some 50 posts (of 2500) and the me.reapint userinformation feature does not work no more.

[Jim Doherty]

Hello

I have noticed that access usually "stops responding" when i activiate large SQL operations.
This is a problem in the case that i want i. e. a postcounter which tells the user how many post that are left in the update process.

I usally use this type of code.

Dim RST as NEW ADODB.Recordset

RST.open sometable, , CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    do untill RST.eof
    with RST
                (do something, maybe a dlookup feature)
     i = i +1
     me.etk_information.caption = "Working on post " & i & " of a total " & totalpost & " posts"
me.repaint
loop
RST.close
Set RST = nothing

The SQL code does exactly what it supposed to, except that access "stops responding" after a some 50 posts (of 2500) and the me.reapint userinformation feature does not work no more.

you are remaining on the first row of your rst in the do loop you need to move to the next row using using rst.movenext that way you move down one row until you get to the end (rst.eof)

Jim

[MrDeej]

Off course hehe..

I have done that in the real code. I justed typed it down on forum by memory.

But even tough i still have a problem with access stops responding untill it is finished with the SQL operation.

[MrDeej]

Dim db   As DAO.Database
Dim rst  As DAO.Recordset
Dim DagensDato As Date
Dim DagAvMnd As String
Dim TaesUtInnen As String
Dim TaesUtInnenAstraFormat As Integer


datodag = Left(Date, 2)
MånedDag = Mid(Date, 3, 2)
år = Right(Date, 4)


nestemnd = DateAdd("m", 1, Date)
nestemndmnd = Mid(nestemnd, 4, 2)
Nesteår = DateAdd("m", 1, Date)
nesteårår = Right(Nesteår, 4)
dennemnd = Mid(Date, 4, 2)
detteår = Right(Date, 4)


If datodag > 15 Then
    TaesUtInnen = "01." & nestemndmnd & "." & nesteårår
ElseIf datodag < 15 Then
    TaesUtInnen = "01." & dennemnd & "." & detteår
End If


DoCmd.RunSQL "DELETE * FROM [IBD temp utløpsdatoer]"
    
    
  Set db = Access.Application.CurrentDb
  Set rst = db.OpenRecordset("tbl lpst00pf TDO", dbOpenDynaset)

  With rst
    .MoveLast
    .MoveFirst
            Dim rst3 As New ADODB.Recordset
            rst3.Open "[IBD temp utløpsdatoer]", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

    Do While .EOF = False
    vnr = !Varenr
    utl = !Utløpsdato
    
    If ![Lagerbeholdning] > 0 Then
    check = DLookup("[Antall måneder holdbarhet minimum]", "IBD tbl holdbarhet unntaksliste", "Varenr=" & vnr)

        If Len(utl) = 7 Then
        utl = utl
        ElseIf Len(utl) = 6 Then
        utl = "0" & utl
        ElseIf Len(utl) = 5 Then
        utl = "00" & utl
        ElseIf Len(utl) = 4 Then
        utl = "000" & utl
        ElseIf Len(utl) = 3 Then
        utl = "0000" & utl
        ElseIf Len(utl) = 2 Then
        utl = "00000" & utl
        ElseIf Len(utl) = 1 Then
        utl = "000000" & utl
        ElseIf Len(utl) = 0 Then
        utl = "0000000" & utl
        End If
        
        If Not IsNull(check) Then
            TaesUtInnen2 = DateAdd("m", check, TaesUtInnen)
            TaesUtInnenAstraFormat2 = "1" & Right(TaesUtInnen2, 2) & Mid(TaesUtInnen2, 4, 2) & Left(TaesUtInnen2, 2)
        ElseIf IsNull(check) Then
            TaesUtInnen2 = DateAdd("m", 3, TaesUtInnen)
            TaesUtInnenAstraFormat2 = "1" & Right(TaesUtInnen2, 2) & Mid(TaesUtInnen2, 4, 2) & Left(TaesUtInnen2, 2)
        End If
        
        If utl <= TaesUtInnenAstraFormat2 Then
                With rst3
                .AddNew
                ![Varenr] = vnr
                ![Utløpsdato] = utl
                ![Lakt inn] = Now
                ![Taes ut dato] = TaesUtInnen
                If Not IsNull(check) Then
                ![unntak?] = -1
                ![unntaksdato] = check
                End If
                i = i + 1
                Me.etk_informasjon.Caption = "Arbeider med post " & i
                Me.Repaint
                .Update
                End With
        End If
        End If
            .MoveNext
    Loop
  End With
  
            rst3.Close
            Set rst3 = Nothing

  Set rst = Nothing
  Set db = Nothing
End Sub

This is how my real code looks like.

[FishVal]

Hi, there.

Your progress counter is being updated only

If utl <= TaesUtInnenAstraFormat2

Are you sure you have a "situation" ?

[MrDeej]

Hi, there.

Your progress counter is being updated only

If utl <= TaesUtInnenAstraFormat2

Are you sure you have a "situation" ?

Good notice! I moved the count-function. But still it goes to 85 and then Access will not lett me see the counting anymore (althoug it is still updating the records)

[Jim Doherty]

Well without disecting everything there is to disect about the design of the database personally I'm pretty much against using multiple uses of Dlookup in loops particularly with large datasets. Each lookup as a processed action to take has to be performed in isolation so if its 100 rows that possibly one hundred or one thousand transactions repeated per dataset depending on the row size of course

I know it looks tempting and easy enough to get a return value using 'one line' of code like that but the functions themselves are not as wholly efficient as they could be and postively crawl on attached tables.

In addition there is a performance hit if you are typically looking up something that may not be indexed and so on

I prefer to design in favour of SQL joins on indexed columns where at all possible to get multiple values returned as a batch in one hit storing them in variables, arrays if needs be and applying the data at run time from memory.

For instance one method might be to store a table of comparables as I call them so that where for instance in this case you are if... then... elsing as a long block of values and its all hardcoded in, you might as well store them in a table and compare data values against each other using a join in SQL it gives you the flexibility of being able to add to the table without having to touch the code again. Technique is probably where I'm coming from on this something very difficult to pass on particurlay where you have an advanced system as yours appears to be?

Dare I say it if you have benchmark tested it as is, and its not performing maybe it back to the drawing board on design? I know its not much help from me but we have all been there. I know many times the solution hits you in the face on a rethink It may even make you think of getting as near to SQL as you can ie: if a condition is whatever you want it to be then using an UPDATE query in SQL as a batch is going to be considerably faster ... rather than singular updates where you save each time in a loop for each row and so on.

Regards

Jim

[MrDeej]

I have know learned a way to get access to respond again.

I just put DoEvents in the Loop code, then access will update the graphic and tell me what it is doing :=)

I will give myself the "best answer" in this tread :D