By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,271 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

Access stoped responding - SQL problem

100+
P: 157
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.
Expand|Select|Wrap|Line Numbers
  1. Dim RST as NEW ADODB.Recordset
  2.  
  3. RST.open sometable, , CurrentProject.Connection, adOpenDynamic, adLockOptimistic
  4.     do untill RST.eof
  5.     with RST
  6.                 (do something, maybe a dlookup feature)
  7.      i = i +1
  8.      me.etk_information.caption = "Working on post " & i & " of a total " & totalpost & " posts"
  9. me.repaint
  10. loop
  11. RST.close
  12. Set RST = nothing
  13.  
  14.  
  15.  

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.
Sep 4 '07 #1
Share this Question
Share on Google+
7 Replies


Jim Doherty
Expert 100+
P: 897
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.
Expand|Select|Wrap|Line Numbers
  1. Dim RST as NEW ADODB.Recordset
  2.  
  3. RST.open sometable, , CurrentProject.Connection, adOpenDynamic, adLockOptimistic
  4.     do untill RST.eof
  5.     with RST
  6.                 (do something, maybe a dlookup feature)
  7.      i = i +1
  8.      me.etk_information.caption = "Working on post " & i & " of a total " & totalpost & " posts"
  9. me.repaint
  10. loop
  11. RST.close
  12. Set RST = nothing
  13.  
  14.  
  15.  

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
Sep 4 '07 #2

100+
P: 157
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.
Sep 4 '07 #3

100+
P: 157
Expand|Select|Wrap|Line Numbers
  1. Dim db   As DAO.Database
  2. Dim rst  As DAO.Recordset
  3. Dim DagensDato As Date
  4. Dim DagAvMnd As String
  5. Dim TaesUtInnen As String
  6. Dim TaesUtInnenAstraFormat As Integer
  7.  
  8.  
  9. datodag = Left(Date, 2)
  10. MånedDag = Mid(Date, 3, 2)
  11. år = Right(Date, 4)
  12.  
  13.  
  14. nestemnd = DateAdd("m", 1, Date)
  15. nestemndmnd = Mid(nestemnd, 4, 2)
  16. Nesteår = DateAdd("m", 1, Date)
  17. nesteårår = Right(Nesteår, 4)
  18. dennemnd = Mid(Date, 4, 2)
  19. detteår = Right(Date, 4)
  20.  
  21.  
  22. If datodag > 15 Then
  23.     TaesUtInnen = "01." & nestemndmnd & "." & nesteårår
  24. ElseIf datodag < 15 Then
  25.     TaesUtInnen = "01." & dennemnd & "." & detteår
  26. End If
  27.  
  28.  
  29. DoCmd.RunSQL "DELETE * FROM [IBD temp utløpsdatoer]"
  30.  
  31.  
  32.   Set db = Access.Application.CurrentDb
  33.   Set rst = db.OpenRecordset("tbl lpst00pf TDO", dbOpenDynaset)
  34.  
  35.   With rst
  36.     .MoveLast
  37.     .MoveFirst
  38.             Dim rst3 As New ADODB.Recordset
  39.             rst3.Open "[IBD temp utløpsdatoer]", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
  40.  
  41.     Do While .EOF = False
  42.     vnr = !Varenr
  43.     utl = !Utløpsdato
  44.  
  45.     If ![Lagerbeholdning] > 0 Then
  46.     check = DLookup("[Antall måneder holdbarhet minimum]", "IBD tbl holdbarhet unntaksliste", "Varenr=" & vnr)
  47.  
  48.         If Len(utl) = 7 Then
  49.         utl = utl
  50.         ElseIf Len(utl) = 6 Then
  51.         utl = "0" & utl
  52.         ElseIf Len(utl) = 5 Then
  53.         utl = "00" & utl
  54.         ElseIf Len(utl) = 4 Then
  55.         utl = "000" & utl
  56.         ElseIf Len(utl) = 3 Then
  57.         utl = "0000" & utl
  58.         ElseIf Len(utl) = 2 Then
  59.         utl = "00000" & utl
  60.         ElseIf Len(utl) = 1 Then
  61.         utl = "000000" & utl
  62.         ElseIf Len(utl) = 0 Then
  63.         utl = "0000000" & utl
  64.         End If
  65.  
  66.         If Not IsNull(check) Then
  67.             TaesUtInnen2 = DateAdd("m", check, TaesUtInnen)
  68.             TaesUtInnenAstraFormat2 = "1" & Right(TaesUtInnen2, 2) & Mid(TaesUtInnen2, 4, 2) & Left(TaesUtInnen2, 2)
  69.         ElseIf IsNull(check) Then
  70.             TaesUtInnen2 = DateAdd("m", 3, TaesUtInnen)
  71.             TaesUtInnenAstraFormat2 = "1" & Right(TaesUtInnen2, 2) & Mid(TaesUtInnen2, 4, 2) & Left(TaesUtInnen2, 2)
  72.         End If
  73.  
  74.         If utl <= TaesUtInnenAstraFormat2 Then
  75.                 With rst3
  76.                 .AddNew
  77.                 ![Varenr] = vnr
  78.                 ![Utløpsdato] = utl
  79.                 ![Lakt inn] = Now
  80.                 ![Taes ut dato] = TaesUtInnen
  81.                 If Not IsNull(check) Then
  82.                 ![unntak?] = -1
  83.                 ![unntaksdato] = check
  84.                 End If
  85.                 i = i + 1
  86.                 Me.etk_informasjon.Caption = "Arbeider med post " & i
  87.                 Me.Repaint
  88.                 .Update
  89.                 End With
  90.         End If
  91.         End If
  92.             .MoveNext
  93.     Loop
  94.   End With
  95.  
  96.             rst3.Close
  97.             Set rst3 = Nothing
  98.  
  99.   Set rst = Nothing
  100.   Set db = Nothing
  101. End Sub

This is how my real code looks like.
Sep 4 '07 #4

FishVal
Expert 2.5K+
P: 2,653
Hi, there.

Your progress counter is being updated only
Expand|Select|Wrap|Line Numbers
  1. If utl <= TaesUtInnenAstraFormat2
  2.  
Are you sure you have a "situation"?
Sep 4 '07 #5

100+
P: 157
Hi, there.

Your progress counter is being updated only
Expand|Select|Wrap|Line Numbers
  1. If utl <= TaesUtInnenAstraFormat2
  2.  
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)
Attached Images
File Type: jpg t1.jpg (19.0 KB, 185 views)
Sep 4 '07 #6

Jim Doherty
Expert 100+
P: 897
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
Sep 4 '07 #7

100+
P: 157
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
Feb 25 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.