Report take long time to run and hangs up the system 1

[McCabeTC]

Hello everyone,

I have a report that is based in several calculations done through VBA. It takes forever to run it from the Network, and it hangs up the system completely (I can't not even minimize the screen).
Did anybody else experience this problem before? If so, knows of a solution for it?
I would greatly appreciate.

Thanks!

TM

 

[wtmckown]

Many things can cause this type of problem. You mention VBA and network. Are you making database calls within a programatic loop that accesses a remote .mdb file? Bad plan if you are. Better to get all the data you need with a single call and then work with it on the client side. On a side note......if you are using a loop that takes time you can use "DOEVENTS" at the top of the loop to return control to the user and OS while your report is running.

 

[McCabeTC]

WTMCKOWN,

Thank you so much for the "DoEvents" suggestion, it was very helpful!
Now, can you ellaborate a little more on the "getting the data in a single call and then work it on the client side" suggestion? Are you talking about having a recordset established at the beginning with all the data needed for the calculations in the report, or are you talking about having a front end/back end application where the report would run from the client machine?

Thank you for any extra help you can give me on that.

TM

 

[wtmckown]

If you would explain your situation and/or post your code I will be happy to point you in the right direction.

 

[McCabeTC]

My database has two main tables: a CLIENT table and VISIT table. Each client can have one or more "visit records". Most of the calculations that are done through my report involves information gathered from these 2 tables. The calculations are very simple (counter increment type), but I do need to go through every single client record, and every single visit record for each client, to get all the counter that are displayed in the report.
Here it is some sample code:

If (Loc > 0) Then
strSQL = "SELECT * FROM ActiveClient WHERE Location = " & Loc & " ORDER BY Id"
Else
strSQL = "SELECT * FROM ActiveClient ORDER BY Id"
End If
Set rsClient = dbs.OpenRecordset(strSQL)
'Rewind the recordset
rsClient.Requery
'Process one record at a time...


While Not (rsClient.EOF)
DoEvents
'Establish a recordset that includes all the visits for the current client and order
'the visits by date ascending order (from the 1st visit to the last).
strSQL = "SELECT * FROM ActiveVisit WHERE ClientId = " & rsClient.Fields("Id&quot.Value
Set rsSource = dbs.OpenRecordset(strSQL)
'Rewind the recordset and check if it is empty. If it is, skip to next record (no visits to
'check).
rsSource.Requery
If rsSource.EOF Then
'MsgBox "Problem: a client was found with no recorded visit(s)."
GoTo Continue
End If
'If date of 1st visit falls in the year chosen, use the record
If (rsSource.Fields(&quot;Date&quot.Value >= YearStartDate And rsSource.Fields(&quot;Date&quot.Value <= YearEndDate And (rsSource.Fields(&quot;Abbreviation&quot.Value <> &quot;E&quot) Then
'Add to Total No. of Women Screened.
Tot1 = Tot1 + 1
'If this client was approved for PEPW, add to the Total No. of
'Approvals for PEPW and to the Total No Referred to ES
If (rsClient.Fields(&quot;ApprvPEPW&quot.Value = 1) Then
Tot2 = Tot2 + 1
Tot6 = Tot6 + 1

I hope that gives you an idea of the &quot;mess&quot; I created.

I am also wondering if the network we are on is aggravating the problem, because when I open it and run the report, it takes about 10 minutes (I am in the building next door to the server). When a user does the same, from 10 miles away, it takes an hour.

Do you think rebuilding the system in SQL Server, using VB as interface, and Crystal Reports would be a better choice?

Thank you so much for taking the time to help me!

TM

 

[wtmckown]

I'm assuming that you are opening an Access database that contains both your tables and your report and that it is on a remote machine. Bear in mind that each time that you ececute a query the entire table(s) will be pulled across the net from the host to your machine. That's why it is not a good idea to ececute your query in a loop. Here's a few suggestions to cut down on ececution time and network transfer time:

1. It appears to me that your first recordset is used to get the Ids of the client records. If this is the case then I suggest that you select only the Id field instead of &quot;*&quot;. Still build the recordset but step it to string together the Id's.

ie.
dim strIDs as string
while not rst.eof
strIDs=strIDs & rst(&quot;ID&quot & &quot;,&quot;
rst.movenext
wend
strIDs = strIds & &quot;-1&quot;
rst.close

Now instead of a loop use something likr this:

strsql=&quot;Select * from ActiveVisits Where ClientID IN(&quot; & strIDs & &quot;&quot;
me.recordsource = strsql

In this manner you are making only 2 calls to the database instead of a number of calls equal to the number of records in the first recordset.

There is still the matter of the variable that you are incrementing in the loop. I recomment using the DCount domain aggregate function as the control source of the report control used to display the value if possible. Perhaps something like:
=dcount(&quot;*&quot;,&quot;[ActiveVisits]&quot;,&quot;[Date]Between [YearStartDate]and[YearEndDate]and [Abbreviation]<>'E'and [ID]=&quot; & txtID & &quot;&quot . This would be used in a fashion whereby txtID would be the current client number being printed on your report (in a grouping or row). Please repair the syntax and add your exact naming. If you need more help just reply and I'll do what I can.

If you are a SQL purist then execute a query that will return the values that you need but do the math in the query instead of stepping a recordset and by all means avoid using a loop that makes db calls when your db is Access and it is remote. Your goal should be to get the job done with as few db calls as possible. Then use a recordset to get the values returned by the query and assign them to report fields, variables, etc. as required.