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.
7 1596
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
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.
- 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.
Hi, there.
Your progress counter is being updated only -
If utl <= TaesUtInnenAstraFormat2
-
Are you sure you have a "situation"?
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)
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Joe A |
last post by:
I'm using Access 2002 on Windows XP PC, 500 megs ram, Front end/back
end app.
I have a simple form that draws a thermometer to indicate progress of
code that is running. The thermometer form...
|
by: bettervssremoting |
last post by:
To view the full article, please visit
http://www.BetterVssRemoting.com
Better VSS Remote Access Tool including SourceOffSite, SourceAnyWhere
and VSS Remoting
This article makes a detailed...
|
by: [Yosi] |
last post by:
I have some unreasonable issue while reading access component from other
thread.
I have a Form class which includes TreeView, this tree view includes some
TreeNodes , lets say 2 .
In my Form...
|
by: dBNovice |
last post by:
Does anyone know a good site where I can find a MS Access forum so I
can get additional info about dB design with Access.
|
by: Chris |
last post by:
Thank you, for your answer.
The exactly message is , Run time error 3734,
and in free translation :
"The DB is in a status from the user "me", on PC "mine", that stoped the
open and lock it."...
|
by: Jordan S. |
last post by:
SQL Server will be used as the back-end database to a non trivial client
application.
In question is the choice of client application:
I need to be able to speak intelligently about when one...
|
by: Alan Mailer |
last post by:
A project I'm working on is going to use VB6 as a front end. The back
end is going to be pre-existing MS Access 2002 database tables which
already have records in them *but do not have any...
|
by: ollyb303 |
last post by:
Hi,
I am having a bit of a problem with TransferText macro.
I am using TransferText, Export Delimited (no field names) to export the results of a query as a .csv file.
The query is based on...
|
by: sachinkale123 |
last post by:
I want to stop Sql server Agent Windows service.
I am using below code to stop or start the windows service. Which is I am doing sucessfully.
ManagedComputer mc = default(ManagedComputer);
...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |