Software

General discussion

Gravatar
0 Votes
Locked

distributed query based on SQLServer DB and Excel sheet returns error ...

By pavel.dobrokhotov ·
Dear Sirs,
can you pls help me with this...
I have a distributed query in my SQL Server database which is run by stored procedure and it
always works good if run from query analizer...
This query refers to native SQL Server table and an Excel file presented by Linked server. If Excel file have 20 rows or so the stored procedure works OK... If 1 more row is added to Exel this error ocurrs when run from MS Access 2000 ADP-file :
"There was a problem accessing a property or method of the OLE object." after 3 minutes of execution....
I think it is connected somehow with query execution timeout...But I checked connection properies in my adp-file and set them = 600 seconds...Why when executed in Query analyzer it's OK and when executed under ADP-file this error ocurrs, and in case when rows in Excel file exceed some limit? What you think?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

gravatar
Collapse -

More info needed

by gustsmits In reply to distributed query based o ...

How do you run your SQL Server stored procedure? Do you use a bound form? ...
When you bind a form to an ActiveX Data Objects (ADO) recordset in Microsoft Access, data errors that occur on the form are not available within the ADO connection's Errors collection. You will always see the "There was a problem accessing a property or method of the OLE object." message. See http://support.microsoft.com/default.aspx?scid=kb;en-us;202868 for more details.

More info about the real SQL Server-error can be obtained if you use the ADO recordset RecordChangeComplete event with your form's recordset. See at http://support.microsoft.com/default.aspx?scid=kb;en-us;301693 for more details.
I don't know if this will help you but it can be a start.

gravatar
Collapse -

the right answer

by pavel.dobrokhotov In reply to More info needed

I managed to solve this problem meself...
The problem was in ADP-file Tools/Options/Advanced menu...
I had to set "OLE/DDE Timeout (Sec)", option
to 300 (sec) which is maximum posible value, and
and also "Refresh Interval (Sec)" to = 30 (sec) or less...
Same thing could be also done at a run-time by code:
Application.SetOption "OLE/DDE Timeout (Sec)", 300
Application.SetOption "Refresh Interval (Sec)", 30
Thank you anyway for considering the problem...

gravatar
Collapse -

the right answer

by pavel.dobrokhotov In reply to More info needed

I managed to solve this problem meself...
The problem was in ADP-file Tools/Options/Advanced menu...
I had to set "OLE/DDE Timeout (Sec)", option
to 300 (sec)which is maximum posible value (instead of 180 by default), and also set "Refresh Interval (Sec)" to = 30 (sec) or less...
Same thing could be also done at a run-time by code:
Application.SetOption "OLE/DDE Timeout (Sec)", 300
Application.SetOption "Refresh Interval (Sec)", 30
Thank you anyway for considering the problem...

gravatar
Collapse -

Excel - Rows Limit

by rajendras In reply to distributed query based o ...

Excel I think can handle a maximum of 65000 rows.

Is your query returning more than 65000 rows.

Back to Software Forum

Start or search

Related Discussions

Related Forums