April 26, 2010 at 9:01 am
Dear all,
I produced a MS Access 2003 ADP project that my client is testing in her environment.
Everything is fine apart from a query that won't open properly.
It works fine in my Dev environment and the symptom is not easy...
I am told that when opening the Query (a stored procedure without any parameter), Access shows the Hourglass for a few seconds and then... nothing!
Is there any information that Access might have stored anywhere with details of the "error"?
Or is there a way to start Access that might give more information about the problem?
Many Thanks
Eric
PS: not sure if that's relevant but my customer does not have admin rights on her machine
April 26, 2010 at 12:30 pm
April 26, 2010 at 3:52 pm
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?
May 11, 2010 at 1:48 am
If you change the timeout to 0 it will never timeout
May 13, 2010 at 1:06 pm
Just for the record, my final solution has been to instantiate Excel through VBA and send the result of my ADO request, hence a recordset, straight to excel.
This seems to bypass Access somehow, resulting in much faster result.
My initial problem seemed to be Access itself getting its nickers in a twist on the number of columns...
Cheers
Eric
July 6, 2010 at 1:06 pm
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.
July 6, 2010 at 3:30 pm
I must admit that 150 columns sounds bizarre but that's because the end user wants that data as a crosstab table.
I do a kind of pivot dynamically. There are not 150 columns, it's created on the fly.
The speed problem only happens if the connection between client and server is particularly slow.
In that case, users logon through VPN and complain about it with strictly all applications.
This is not a problem with Sql Server, only MS Access seems to struggle (I wrote a small .Net winform app that flies through that dataset regardless of network conditions).
However, I am still disappointed by the lack of performance of the Access client in those circumstances and was hoping there might be some "trick" to make it behave more like my TSql client or Winform test...
:crying:
PS: I am also trying to get their VPN speed problem addressed
July 6, 2010 at 7:16 pm
Eric Mamet (7/6/2010)
I must admit that 150 columns sounds bizarre but that's because the end user wants that data as a crosstab table.I do a kind of pivot dynamically. There are not 150 columns, it's created on the fly.
The speed problem only happens if the connection between client and server is particularly slow.
In that case, users logon through VPN and complain about it with strictly all applications.
This is not a problem with Sql Server, only MS Access seems to struggle (I wrote a small .Net winform app that flies through that dataset regardless of network conditions).
However, I am still disappointed by the lack of performance of the Access client in those circumstances and was hoping there might be some "trick" to make it behave more like my TSql client or Winform test...
:crying:
PS: I am also trying to get their VPN speed problem addressed
Try posting at this forum, there are several Access MVP's who might be able to chime in.
http://social.msdn.microsoft.com/Forums/en-US/accessdev/threads
I still think that you should thoroughly question the clients request for that particular cross-tab and did into the real questions that he/she is wanting to answer. I just can't see how anyone can usefully comprehend the data across 150 columns.
July 7, 2010 at 1:41 am
Have you tried to increase the OLE/DDE timeout value? Just a guess but worth a try. The following example sets the timeout value 60seconds.
Application.SetOption "OLE/DDE Timeout (Sec)", 60
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply