Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Excel VBA
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 6:18am
Edited 16-Jan-13 7:45am
RedDk10.3K
v2
Comments
RedDK at 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?
Rate this: bad
good
Please Sign up or sign in to vote.

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
  Permalink  
v5
Comments
RedDK at 16-Jan-13 14:12pm
   
ConnectionStringsdotcom ... amazing, isn't it?
BC @ CV at 16-Jan-13 14:36pm
   
Yes indeed. The one stop shopping destination for solving connection conundrums!
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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

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

  Print Answers RSS
0 OriginalGriff 350
1 Jochen Arndt 150
2 PIEBALDconsult 110
3 Richard MacCutchan 105
4 Sergey Alexandrovich Kryukov 104
0 OriginalGriff 6,055
1 DamithSL 4,621
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,500
4 Sergey Alexandrovich Kryukov 3,294


Advertise | Privacy | Mobile
Web02 | 2.8.141220.1 | Last Updated 17 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100