SQL Command excel VBA timeout

Question

0.00/5 (No votes)

See more:

Hi,
Can anyone tell me how to set the command timeout to 0, or any other number for that matter using the following recordset connection and open string?

objConn.Open "Driver={SQL Server}" & _
           "Server=x.x.x.x;" & _
           "Address=x.x.x.x,1433;" & _
           "Network=DBMSSOCN;" & _
           "Database=yyy;" & _
           "Uid=user" & _
           "Pwd=pass"

sSQL = "SELECT InvoiceYear, InvoiceMonth, InvoiceDay, InvoiceDate, InstituteName, CourseName, ItemNet , ItemTax FROM dbo.Table_Sales ORDER BY InvoiceDate"

rsData.Open sSQL, objConn, adLockReadOnly, adCmdText

Many thanks in advance
Martin

Posted 16-Jan-13 5:18am

Hutch2013

Updated 16-Jan-13 6:45am

RedDk

v2

Add a Solution

Comments

RedDk 16-Jan-13 12:49pm

If by timeout you mean "to zero because when I run this query I get a timeout error" I'd say you need to fix the connection string. And by the way, what version of Excel are you running and on what Windows?

Might be a driver issue too. 32-bit?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

BC @ CV · Answer 1 · 2013-01-16T07:40:00

Solution 1

Try changing your connection string to one found on this page.

Perhaps setting the Connect Timeout parameter to 300 seconds (5min) like this:
"Server=ServerName\InstanceName;Database=yyy;User Id=user;Password=pass;Connection Timeout=300;"

Alternatively you could set one of these properties found on the ADO connection object:
objConn.ConnectionTimeout
objConn.CommandTimeout

Posted 16-Jan-13 7:40am

BC @ CV

Updated 16-Jan-13 7:53am

v5

Comments

RedDk 16-Jan-13 14:12pm

ConnectionStringsdotcom ... amazing, isn't it?

BC @ CV 16-Jan-13 14:36pm

Yes indeed. The one stop shopping destination for solving connection conundrums!

Hutch2013 · Answer 2 · 2013-01-17T07:19:00

Solution 2

In case anyone interested I managed to solve the problem by changing the provider
Provider=SQLNCLI10

Posted 17-Jan-13 7:19am

Hutch2013