Report with subreport fails randomly

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm working in Access 2002 SP3 to develop a report with a subreport. The
tables in the database are linked tables that draw their data from SQL Server
2000. The main report is based on one query, and in code it opens another
query. The subreport opens a 3rd query.

The main report seems to switch randomly between working and not working. At
times it opens and displays correctly, and at other times, it fails with
error message, "Connection failed: ...SQL Server does not exist or access
denied." In some cases it starts or stops working without my having changed
anything. It's unlikely that anyone else is changing any data in this
database.

The child report and the queries that the main report and the subreport are
based on, all work when opened individually, even when the main report is not
working.

If I delete the child report from the main report, the main report works. If
I comment out the main report's code to open its second recordset, both the
main report and the child report display correctly.

Any suggestions what the problem is?

Thanks in advance.
 
This may be an issue where possibly one of the queries is timing out. You
can set the timeout for the query (command) opened in code by setting the
Command's CommandTimeout property. Too it may be simply a contention issue,
can you move the code from the current event used to another area of the
report?
 
Thanks for the ideas. I think you're right about it being a contention issue.
I'm not sure what's being contended over, though. Some more experimenting
revealed that if any sizeable linked tables are open the report consistently
fails. Also when I've taken steps to simplify the queries, such as replacing
a crosstab with a simple select, the frequency of the problem has gone down.
It's now CLOSE to zero as long as I don't open an tables, but I don't know
that the users will be willing to comply with that as a condition in the
production environment.

I've had some previous issues that I've been able to resolve by moving code
from one event to another. In this case, nothing jumps out at me as likely to
be helped by that. I'm opening a recordset from the main report's
ReportHeader_Format event, and that's about the only resource-intensive
operation I"m doing in code. Open to any suggestions on this.

Again, thanks for the help.
 
Follow up note: I moved some of the logic from Access queries to a SQL view
(filtering out some data and limiting the length of some fields) and haven't
had any trouble with it since.
 
Back
Top