Long Running VB Codes...

[kimosavi]

Hi,

I have some subrutines that are time extensive. It is Ok due to the large amount of data they process.

my problem is that while they are running Access says (not responding) until the code finishes. Some users think Access has crashed and tries to stop the code or restart the PC.

is there a way to prevent this "not responding" from happening?

is there a way to substitute the "not responding" with a "please wait window" or progress bar?

can I run the code like a background process?

thanks for your input in advance!

Kimo

 

[gemma-the-husky]

among other things

a) look at the subs carefully to make them as efficient as possible - what are the subs doing

b) display the hourglass

c) you can add aprogress bar, if you know how many iterations you have

b) look at doevents - put a doevents inside the loop/code somewhere

 

[kimosavi]

thanks gemma-the-husky for your reply.

well the problem is not so much within loops, but running extensive SQL statements.

most of the code are simply

MSQL = "SELECT XXX FROM XX GROUP BY XX"

Currentdb.Execute MSQL

so the DB is about 1.9GB on a split file, so it takes a while to run queries...

it is not a matter of time or to improve the code, but its a matter on how access is behaving in regards to the wait time.

if i put a progress bar before running the code the same thing will happen...

that's why I asked ...

is there a way to prevent this "not responding" from happening? like changing the "not responding" with a "please wait window" or progress bar? or running the code like a background process?

Thanks again.

 

[RuralGuy]

You can certainly put up a form explaining that the application *may* report as Not Responding but just give it some time. Then take it down when you are done. Any way to cut down the recordset for your queries?

 

[kimosavi]

Thanks RuralGuy,

I had already updated the form msgs to reflect Access behavior, but it takes way from the look and feel of the tool.

This tool is meant to analyse data sets ... data could be 10MB to 2GB, so in cases of small data set the performance will be nice, but the 2GB files are the stone in the shoe.

I am building a performance table to record the regular process times divided by the number of records process, this will give the user an idea of how long the query might run and for them to expect the results in the time estimated.

even though is a good idea, i will love to see that "not responding" message go away....

 

[RuralGuy]

As Gemma said, unless you can slip in some DoEvents, it is a windows issue.

 

[gemma-the-husky]

you can use doevents inside a sub loop say, but queries seem to be pretty well self contained, and you just have to wait for them to finish.

if these are regular things though, summaries of some type, perhaps, then it may be worth considering building a compressed summary/history of your data of some form, that you could then use for the queries you are describing. - even though this is un-normalised

 

[kimosavi]

Thank you all for the replies.

I already built the proper tables as "permanent views" since the data is not transactional.

the problem is generating those temp tables is when the time comsuming process starts.

I guess i will just have to deal with it, put some messages and some time estimates before running.

thanks!

 

[dkinley]

... you can use doevents inside a sub loop ...

I had encountered the same problem so moved from a query and used DAO. Although not the best implementation, the loops allowed me to insert DoEvents so I would not get the 'Not Responding' bit.

I also had a label sitting on the form and used a counter in the loop. At the end of each loop, I used a Me.lblCounter.Caption = intCounter and Me.Repaint so the counter would increase to show the user something was going on. Also when the counter reached rst.Count - 1 the caption would show a completed message.

Again, not very efficient in the binary sense - but with the hourglass, I did it for the user interface so they wouldn't force a database close.

Just a halphazard idea and in no way formally recommending - just sharing an experience ...
-dK

 

[RuralGuy]

...and I was just going to recommend a DAO.Recordset loop. I like the solution and I'll bet there is not much of a time penalty to pay.

 

[dkinley]

I don't recall if there was a time difference - I did it for the user interface aspect so the trade off was worth it for my particular circumstance. I should add that the admin would force a close on the database thus corrupting the data.

For mine, the database was split - but the administrative user that ran the updates hosted the back end in another directory on his machine. The tradeoff might be if it's split on an actual network and if the query is optimized or not - I am not sure how DAO works in that instance.

-dK

 

[kimosavi]

Ok guys, some numbers to look at...

I created a simple query that sums the total dollar amounts.

I have the data on both the local hard drive and on a SQL server.

QT: Query Time

1. running a VIEW on ADP project. QT: 6-8sec
2. running a QUERY local hard drive. QT: 45-55sec
3. running a ADODB connection via VBA to SQL (ADO Engine). QT : 8-9sec
4. running a ODBC connection via VBA to SQL (CurrentDB Engine). QT: 2:30m!
5. running a JET connection via VBA to Local (CurrentDB Engine). QT: 23-31sec
6. running a DAO connection via VBA to Local (CurrentDB Engine). QT: 31-34sec

there is not much difference on Jet and DAO, but loading the queries to the recordset object is were the time is spend.

using DoEvents will not do me any good since the loop interaction are very small or non-existance.

maybe i am not setting the code correctly.

the steps i take (ex. JET)

dim rs as recordset
mSQL = "SELECT XX FROM YY"
set rs = CurrentDB.OpenRecordset(MSQL) <-- 30sec

do while not rs.EOF
' some code
rs.movenext
a= doevents() ????
loop

most of my code are structured like this. most of the cases I run the queries to get a value or a small subset to save it to a temp table.

Saving the data to the table takes no time, but running the query, it does.

So... how will I use the DoEvents then?

 

[RuralGuy]

This is a bit crude but will give you some ideas:

Public Sub FOO()

   Dim Rs As DAO.Recordset
   Dim MySQL As String
   Dim LoopCount As Long
   Dim MySum As Currency

   MySum = 0
   LoopCount = 0
   MySQL = "SELECT Price FROM tblTest;"

   Set Rs = CurrentDb.OpenRecordset(MySQL, dbOpenForwardOnly)  '<-- 30sec

   With Rs
      Do While Not .EOF
         MySum = MySum + !Price
         .MoveNext
         LoopCount = LoopCount + 1
         If LoopCount Mod 3 = 0 Then
            DoEvents
         End If
      Loop
      .Close
   End With
   MsgBox "Total is [" & MySum & "]"
   Set Rs = Nothing
End Sub