Access Query Stops working

Question

I have this Access database that we use to run reports. The report is ran twice a day every working day. For some reason the report will run fine usually in the morning but every now and then it will stop working. I go to debug and it will show a different query failed to run every time. So far there are two ways to fix this. Close the application and re-open, or refresh the linked tables. I would really like to know why this is happening and if possible how to prevent it.

Thanks in advance for any assistance. I can provide more information if requested.

Edit

The tables are connected through ODBC which points to SQL Server 2000. The error messages usually say "Could not connect to --". In between -- should be the ODBC connection name but it doesn't display.

There are about 10 linked tables. The computers are on wired LAN. If I open one of the failed queries and try to run it fails. When I refresh/reopen it works.

When I say go to debug I mean that when the error occurs it gives me two options end/debug. I click debug and it opens up the modules(VBA code) window.

Answer 1

Could someone be changing the definitions of the tables that are linked? If so, that might be the problem.

Answer 2

usually in the morning but every now and then it will stop working

This sounds REAL familiar to me. Perhaps your company servers are so backed up at this time that your reports are giving time out errors? I recommend scheduling your reports at a different time. If your boss says No, tell him/her "Ok, It will continue to run in the morning, but it may not always go out."