CODEWITHSUNDEEP
ms-accessms-access-2003
PowerUser
PowerUser

Reputation: 11801

Extra long query is timing out too often. Help?

Here's how this particular reporting process is supposed to work:

  1. A VBA script tells an Excel file to update it's external data.
  2. The Excel file pulls it's data from an adhoc Access 2003 db (stored on the network), does some formatting to pretty it up, and saves the file to a network drive.
  3. The VBA script emails it to the data-hungry recipients.

I've been doing this for years for now with the sporadic problem that the machine sometimes hiccups on the data pull. These are 2 of the error messages I can get:

As time has gone on, the queries in the adhoc Access db have grown more and more complex. Today, 8 more OR criteria clauses were added to one particular query which already had about 10 of them to begin with. The result is when I tried to run the updated report today, it threw errors 4 times before a successful run.

If I had the option, I would edit the queries directly and see if I could improve performance that way, but that is unlikely for political/security reasons. Hence, I am asking you, oh fine fellow contributors to StackOverflow, for suggestions to make this more reliable.

Upvotes: 0

Views: 195

Answers (1)

mwolfe02
mwolfe02

Reputation: 24237

Your options are pretty limited if you can't improve the queries themselves. The error messages you are getting (and the fact that they are sporadic) seem to indicate the problems may be caused by the fact that you are running them across the network.

If possible, I would try copying the Access data file down to the machine running the script as the first part of the script.

If you are unable to re-link the tables within Access, you can "fool" Access by using the subst command to impersonate their normal network location (assuming, of course, that the current linking is based on mapped drive letters and not UNC's).

Upvotes: 1

Related Questions