Retrieve/create recordset from SQL Server data in Excel vba

Question

I want to access / retrieve / create recordset from SQL Server in Excel vba.

I tried following methods but they return an error.

Code 1:

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String

sConnString = "Provider=sqloledb; Server=192.168.0.204; Database=REPORTdb2"
Set Conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.Open sConnString 
Set rs = conn.Execute("select * from Table1;")

at the line conn.Open sConnString an error occurs:

Invalid authorization specification

Code2:

sConnString = "Provider=SQLOLEDB;Data Source=192.168.0.204;" & _
              "Initial Catalog=ReportDB2;" & _
              "Integrated Security=SSPI;"
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.Open sConnString 
Set rs = conn.Execute("SELECT * FROM Table1;")

It throws an error

Cannot generate SSPI context

Answer 1

The following code requires in the VBE a reference to Microsoft Active Data Objects 2.8 Library or above:

Public Sub AdoTestConnection()
Dim conServer As ADODB.Connection
Dim rstResult As ADODB.Recordset
Dim strDatabase As String
Dim strServer As String
Dim strSQL As String

Set conServer = New ADODB.Connection
conServer.ConnectionString = "PROVIDER=SQLOLEDB; " _
    & "DATA SOURCE=192.168.0.204; " _
    & "INITIAL CATALOG=REPORTdb2; " _
    & "User ID=sa;" _
    & "Password="
On Error GoTo SQL_ConnectionError
conServer.Open
On Error GoTo 0

Set rstResult = New ADODB.Recordset
strSQL = "set nocount on; "
strSQL = strSQL & "select * from Table1;"
rstResult.ActiveConnection = conServer
On Error GoTo SQL_StatementError
rstResult.Open strSQL
On Error GoTo 0

'To copy the result to a sheet you may use the following code
'It will copy your table 'Table1' to the first sheet in your Excel file.
ThisWorkbook.Sheets(1).Range("A1").CopyFromRecordset rstResult

Exit Sub

SQL_ConnectionError:
MsgBox "Problems connecting to the server." & Chr(10) & "Aborting..."
Exit Sub

SQL_StatementError:
MsgBox "Connection established. Yet, there is a problem with the SQL syntax." & Chr(10) & "Aborting..."
Exit Sub

End Sub

With the included error handling for

1. establishing a connection to the SQL server and
2. trying to pass an T-SQL command to the server for processing

you should be able to easily troubleshoot the problem for connecting to the server. The above code will only return a 1 upon success (for a test run). Afterwards, you can substitute your SQL command for the one in the above example.