SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Access 2003: error logging?


Access 2003: error logging?

Author
Message
Eric Mamet
Eric  Mamet
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6621 Visits: 955
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
Koncentrix
Koncentrix
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 40
This often happens if the data is too big... but it could also be corrupted. Does the query bound to a link sql tables?

Koncentrix
[url=http://koncentrix.com][/url]
Eric Mamet
Eric  Mamet
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6621 Visits: 955
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?
Chris Quinn-821458
Chris Quinn-821458
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1992 Visits: 852
If you change the timeout to 0 it will never timeout
Eric Mamet
Eric  Mamet
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6621 Visits: 955
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
david.c.holley
david.c.holley
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1321 Visits: 191
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.
Eric Mamet
Eric  Mamet
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6621 Visits: 955
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
david.c.holley
david.c.holley
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1321 Visits: 191
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.
liebesiech
liebesiech
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1340 Visits: 877
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search