ODBC SQL server driver query timeout expired

Question

I am using the following code in excel VBA and got the timeout expired error. Please help how to resolve the issue

Sub PiConnectorItems_muuk()
Dim oConn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim mssql As String
Dim row As Long
Dim Col As Integer
Dim ws As ThisWorkbook
Set ws = ThisWorkbook
Application.ScreenUpdating = False
Set oConn = New ADODB.Connection
Set rs = New ADODB.Recordset
mssql = "select * from abc"
oConn.ConnectionString = "driver={SQL Server};" & _
"server=ewepwapapisx05\SQLEXPRESS;authenticateduser = TRUE;database=TIJSV"
oConn.ConnectionTimeout = 30
oConn.Open
rs.Open mssql, oConn
If rs.EOF Then
MsgBox "No matching records found."
rs.Close
oConn.Close
Exit Sub
End If
row = 1
Col = 1
    For Each fld In rs.Fields
    Sheet1.Cells(row, Col).Value = fld.Name
    Col = Col + 1
    Next
row = row + 1
Do While Not rs.EOF
Col = 1
    For Each fld In rs.Fields
    Sheet1.Cells(row, Col).Value = fld
    Col = Col + 1
    Next
row = row + 1
rs.MoveNext
Loop
rs.Close
oConn.Close
End Sub

I have searched for a solution to this issue with no results yet.

Answer 1

I'm guessing that there is something wrong with your connection string or with server security settings. Below connection string that worked in my application.

"Provider=SQLOLEDB;Data Source=ServerName\SQLEXPRESS;Initial Catalog=MyDbName;Integrated Security=SSPI"

You may have different server security settings so the connection string above might need adjustments. For more connection strings check this website - it was helpfull for me.

https://www.connectionstrings.com/microsoft-sql-server-odbc-driver/

It also might be good to check ODBC data sources available on your machine, maybe you have other drivers that are easier to use.

if you have admin access to your SQL server you could also create user with access to specific db and use the credentials in connection string. But do it just for testing as there is no way in excel to secure the connection string information.

"Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;"

One more issue I remember with connection between SQL server and Excel/Access was that server had two separate internet connections and sometimes firewall blocked user. Using server IP instead of server name was a temporal solustion.