Eric Mamet (4/26/2010)
I have since discovered more...I have the laptop with me and could do a few experiments.
The problem is "timeout" after about 35 seconds.
I tried without success changing the "General Timeout" to 120 seconds but it still failed after 35 seconds.
I also tried the first timeout I found on the settings but it turned out to be the "connection" timeout and had no effect on this problem as one woud expect.
Then my wife discovered (I know... it's embarassing...) that there is a OLE/DDE Timeout in tools / advanced and we got away with changing this from 30 to 60 seconds however (the equivalent to the ADO command timeout I suppose).
Now, this is only a short term solution because if 30 seconds is not enough today, 60 won't be tomorrow so I need to understand more.
I have this problem with 2 queries (stored procedures without params) that return each about 150 columns in a datasheet view.
Query A returns one row per Account while Query B returns one row per Territory.
There are about 170 accounts split among 11 territories.
Some users have only access to a couple of territories and they don't experience the problem.
Only users with access to all 11 territories get the timeout.
One would then think that the procedure is simply too slow but this is not the case because running the same procedure from the laptop via a simple "home made" .Net sql client returns in a couple of seconds at most.
I could also understand that MS Access struggles a bit with 150 columns x 170 rows but why having the same problem with only 11 rows?
Is there any reason why MS Access struggles with many columns?
Any way to improve this?
"I could also understand that MS Access struggles a bit with 150 columns x 170 rows but why having the same problem with only 11 rows?"
The fact that you're returning 150 columns is a great big HUGE red flag. For the record, the max is 250. While being 1/2 there shouldn't impact performance, from a design standpoing, I seriously question why there's a need for 150 columns. While I can certainly see situations where it could occur, for example my current project where I'm converting multiple child records into multiple column in a SELECT, I question if you've carefully evaulated the need for all 150. While that doesn't solve the problem of the time out, it will go a along why of eliminating it.
FYI - If you're running the backend on the same machine that you're developing on, then it makes perfect sense that the timeout wouldn't be seen in development. I'm willing to bet that if you connected to the production backend from your own machine, then you'll see the time out issues.