Long delays setting recordsource property in VBA

[phxbypdx]

Hey experts -

A brief history - I inherited an application that has tens of thousands of lines of code, more than a hundred forms, even more tables/queries, etc etc. It's far exceed what I would have put into an Access 2002 application (honestly it should be on .NET or converted to web app), and it's not always super efficient. Myself, I'm a PHP & .Net programmer, so jumping back to VBA has been... interesting.

Here's the problem. A form is loaded that calculates the line items, and eventual totals, for an invoice. This was taking upwards of 2.5 minutes to load on a speedy new machine, 6-7 minutes on an old clunker. The processor gets overloaded on the older machines, running at 99% for the entire time. I've set breakpoints and narrowed down the delay to the following:

sql = "SELECT * FROM qryTotalsDisplay WHERE TotalLabel_Group = ""XXXXXXXXXX"""
Set db = CurrentDb()
Me.sfrmWFTotal.Controls("sfrmWFTotalP").Form.RecordSource = Replace(sql, "XXXXXXXXXX", "Parts")

The record source update is run 5 or 6 times, and each one takes 30-60 seconds.

I was able to test a version from January, and this totals screen loaded in 5-8 seconds.

I went to troubleshoot it further today, and now it only takes 5-8 seconds as well. The only thing I ran that I can think would have resolved this was a compact & repair. Can that resolve something like this? Otherwise, what would cause this?

Thanks,

Scott

 

[HiTechCoach]

Scott,

Yes a compact can do that.

Also decompiling the VBA code and re-compiling the code can help. This is a good reason to use a MDE/ACCDE as the front end.

What version of Access?

Is the database split into application and data ?

 

[marlan]

Hi,
I'm not soure it makes a difference, but how about moving the WHERE coulse of the record source to the form filter (providing the RecorSource is form the same table).

 

[gemma-the-husky]

sql = "SELECT * FROM qryTotalsDisplay WHERE TotalLabel_Group = ""XXXXXXXXXX"""
Set db = CurrentDb()
Me.sfrmWFTotal.Controls("sfrmWFTotalP").Form.RecordSource = Replace(sql, "XXXXXXXXXX", "Parts")

what do these lines actually do?

what are you trying to display? presumably as your recordset gets larger and larger, doing some possibly nonindexed text based search is not so efficient.

maybe we can find a better way to do this process?

 

[phxbypdx]

Scott,

Yes a compact can do that.

Also decompiling the VBA code and re-compiling the code can help. This is a good reason to use a MDE/ACCDE as the front end.

What version of Access?

Is the database split into application and data ?

Hey Coach. Thanks for the information. I do use an MDE as the front end (application) with a data back end. This is written in Access 2002 (aka office XP). It's long overdue for an upgrade to .net/sql or php/mysql. The owner of the app brought me in to stabilize the current version so it can be pushed out to his customers. Once done, we'll look at upgrading.

what do these lines actually do?

what are you trying to display? presumably as your recordset gets larger and larger, doing some possibly nonindexed text based search is not so efficient.

maybe we can find a better way to do this process?

There is a query (qryTotalsDisplay) that displays aggregates totals from the results of a number of queries. These lines pull those results from the query, sum the totals, and output to a report.

I agree on the nonindexed text based search being non-optimal. In fact, text searches in general I frown upon as much as possible. The delay wasn't in selecting the records, the delay was actually in the line of code where you were setting the recordsource property. At one point, to test, I just said

Me.sfrmWFTotal.Controls("sfrmWFTotalP").Form.RecordSource = "SELECT * FROM qryTotalsDisplay WHERE TotalLabel_Group = Parts"

This line still took the same 30-60 seconds, which I find odd.