Always getting "connection failed" connection sqlserver using VBA

Question

I am trying to connection sqlserver from VBA program, I refer to the following code to achieve it, but I got the problem: connection failed. Any help. Thank you in advance.

Code:

Sub ADOExcelSQLServer()
     ' Carl SQL Server Connection
     '
     ' FOR THIS CODE TO WORK
     ' In VBE you need to go Tools References and check Microsoft Active X 
    'Data Objects 2.x library

Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Server_Name = "EXCEL-PC\EXCELDEVELOPER" ' Enter your server name here
Database_Name = "AdventureWorksLT2012" ' Enter your database name here
User_ID = "" ' enter your user ID here
Password = "" ' Enter your password here
SQLStr = "SELECT * FROM [SalesLT].[Customer]" ' Enter your SQL here

Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"

rs.Open SQLStr, Cn, adOpenStatic
 ' Dump to spreadsheet
With Worksheets("sheet1").Range("a1:z500") ' Enter your sheet name and range here
    .ClearContents
    .CopyFromRecordset rs
End With
 '            Tidy up
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End Sub

Isn't your server_name "EXCEL-PC" ?

Dy.Lee
– Dy.Lee

2018-11-30 02:25:23 +00:00
Commented Nov 30, 2018 at 2:25 — Dy.Lee
– Dy.Lee, Commented Nov 30, 2018 at 2:25

Dy.Lee · Accepted Answer · 2018-11-30 02:32:38Z

1

test this.

Sub cn()
    Dim con As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rst As New ADODB.Recordset
    Dim i As Integer

    con.ConnectionString = "Provider=SQLOLEDB.1;" _
             & "Server=(local);" _
             & "Database=AdventureWorksLT2012;" _
             & "Integrated Security=SSPI;" _
             & "DataTypeCompatibility=80;"

    con.Open


    Set cmd.ActiveConnection = con
    cmd.CommandText = "SELECT * FROM [SalesLT].[Customer]"
    Set rst = cmd.Execute
    Range("A1").CopyFromRecordset rst

    con.Close
    Set con = Nothing

End Sub

answered Nov 30, 2018 at 2:32

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Simon Li · Accepted Answer · 2018-12-01 02:08:18Z

0

I checked your code and it works for me. So I think it should be a SQL configuration problem e.g. permissions or port issue. You need to test connection status by using sqlserver client and check related connection problem.

answered Dec 1, 2018 at 2:08

Simon Li

3232 silver badges4 bronze badges

Collectives™ on Stack Overflow

Always getting "connection failed" connection sqlserver using VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related