1

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

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
curious K
  • 145
  • 5
  • 15
  • Have you tried to connect to the Server using SSMS in order to validate the IP and that your Windows account is allowed to connect to the server? BTW, I am sure you have set a reference in the VBE to `Microsoft ActiveX Data Objects 2.8 or above`? – Ralph Mar 14 '16 at 12:47
  • sorry I'm new to VBA but I can access using create connection from excel option Data-->from other source-->From Sql Server. There I use ip address only. And it can access perfectly now I want to access data without set the connection file i.e. directly coding from Module. And Ref. is correction i.e. 2.8 – curious K Mar 14 '16 at 13:00
  • If the below solution solved your problem then please close this question like so: http://stackoverflow.com/tour Otherwise, please do let me know what's missing. – Ralph Mar 15 '16 at 15:32

1 Answers1

0

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.

Community
  • 1
  • 1
Ralph
  • 9,284
  • 4
  • 32
  • 42
  • Message Appear : Problems connecting to the server. Aborting... You can see in my Code2 it gives an error for SSPI context – curious K Mar 14 '16 at 13:06
  • Did you set a reference to `Microsoft Active Data Objects 2.8 Library` in the VBE as mentioned in the solution? Also, try changing `INITIAL CATALOG=REPORTdb2;` in the above sample to `INITIAL CATALOG=master;`. – Ralph Mar 14 '16 at 13:07
  • Yes, I already mark for 2.8 Library and for catalog name is correct its REPORTDB2 – curious K Mar 14 '16 at 13:11
  • When you can connect to the server with `Data-->from other source-->From Sql Server` do you select `Use Windows Authentication` or do you use `Use the following User Name and Password`? SSPI means that it is using windows authentication (the first option). If you enter a user name and a password then the VBA code needs to be changed. – Ralph Mar 14 '16 at 13:25
  • Oh! from the option I use user name as sa and password is blank – curious K Mar 14 '16 at 13:26
  • can u correct this code or guide me to access my server database and reterive table data with servername : 192.168.0.204 db name : reportdb2 & table name : Table1 user name: sa password is blank – curious K Mar 14 '16 at 13:32
  • here you mention SSPI means that it is using windows authentication (the first option). If you enter a user name and a password then the VBA code needs to be changed. So what to change if I want to access username as sa with no password. – curious K Mar 14 '16 at 13:39
  • The IP address is already in the above code (as you can see). Also, the database is already in the above code. Now I also changed the username and password for you. I sure hope this resolves all obstacles and you can run the code without problems. – Ralph Mar 14 '16 at 14:07
  • Yes first error removed.It connect to server but it is not populating records from the table. even I check with MsgBox rstResult.RecordCount it is showing -1 and for next statement error message is "subscript out range." – curious K Mar 18 '16 at 12:57
  • I am glad that the question of "connecting to the server" is resolved and fixed. For further questions, please open up a new question and close this one as solved. This Q&A is already way too big to add to it. But you can add a reference here to the new question and I will be sure to help you with that as well. – Ralph Mar 18 '16 at 15:29