May 28, 2002 at 3:08 pm
I have a SQL back-end database with an Access front end. If you keep the Access front end open for more than 3 or 4 minutes, then every procedure which uses the back-end will fail (opening tables, queries, reports, etc.)
I have set the timeouts to the max allowable in Access. Is there any other way to increase functionality here? The users are very frustrated, as they are constantly having to close and re-open the database.
May 28, 2002 at 7:08 pm
Definitely sounds bad. Extending the time out will just mask the problem. You're using linked SQL tables or doing everything via code? Are you using the latest MDAC on the clients (2.6+)? Are you able to connect to SQL using EM or Query Analyzer when the problem happens? What size server and what SQL version are you using?
Andy
May 29, 2002 at 1:07 pm
The tables are linked. Eventually, when some other projects wrap up, I will replace most of the Access stuff with more functional SQL Procedures, but I don't have time to write eveyrthing out in SQL now, due to my workload. But in the meantime I need a fix for this problem.
I'm running SQL Server 2000 on NT 2000. The performance is better when I go through Query Analyzer; but that doesn't help me for reporting out in Access. When the problem happens, it always goes away if I simply close and go back in, but that's obviously cumbersome.
What are MDAC and EM?
May 29, 2002 at 2:10 pm
MDAC is the standard data access from Microsoft. Go to http://www.msdn.microsoft.com and search for MDAC to find the latest version. EM is Enterprise Manager. Have you indexed all your tables? What exact error message do the users get?
Andy
May 29, 2002 at 5:07 pm
Can you tell me how to figure out which MDAC package I have? It's whatever came with the OS.
Anyway, the tables are all fully indexed, and I do have Enterprise Manager, which I use regularly; but the problem only happens in Access. The error message is:
"ODBC-- call failed. [Microsoft][ODBC SQL Server Driver]Timeout Expired(#0)"
May 29, 2002 at 8:20 pm
Not sure where to go from here. I'd start by profiling a session so you can see what Access is sending to SQL, maybe see if its using a lot more connections than you think or using a query that is really slow. Beyond that I think it might make sense to find an Access specific site/newsgroup, they may have some tips to help you narrow it down within Access. Sorry I dont have anything better than that.
Andy
May 30, 2002 at 5:47 am
Check out http://www.microsoft.com/data/download.htm#CCinfo fo the MDAC Component CHecker, this will help you find out your installed version and should help diagnose install issues (which I haven't tested).
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply