Excel connection to SQL Server

[jwise]

Hello,



I am trying to demonstrate the technique to retrieve data from our MS SQL Server and put this data in a worksheet. I essentially got this code from the MS website. The database is the example "pubs" that comes with MS SQL Server, and its table "Authors".

Sub SQL09()
' From MSDN, Example SQL data extract
' http://support.microsoft.com/default...306125&sd=tech
' Set REFERENCE TO ADO
' Object Description
' Connection Refers to the connection to the data source.
' Recordset Refers to the data extracted.
' Command Refers to a stored procedure or SQL statements that need to be executed.
Dim cnPubs As ADODB.Connection
Dim rsPubs As ADODB.Recordset
Dim strConn As String
Set cnPubs = New ADODB.Connection
strConn = "PROVIDER=sqloledb;"
strConn = strConn & "Network Library=dbmssocn;"
strConn = strConn & "DATA SOURCE=300.300.300.300,1433;"
strConn = strConn & "INITIAL CATALOG=pubs;"
strConn = strConn & "Integrated Security=SSPI"
cnPubs.Open strConn
Set rsPubs = New ADODB.Recordset
With rsPubs
   .ActiveConnection = cnPubs
   .Open "SELECT * FROM Authors"
   Sheet1.Range("A1").CopyFromRecordset rsPubs
   .Close
End With
cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
End Sub

I have verified that the database is alive and well, i.e. I can see it from the Server. Obviously I have changed the IP address of the actual data, but it is NOT on my workstation.

The error message looks like:

[DBNETLIB][Connection Open (Connect().]SQL Server does not exist or access denied.

If I go to the Server and look at its security log, I see no attempted logins. I have verified that the server IP address is correct. I would expect to see security messages- is this correct? The Server is handled by a contractor who is not on site. How do I tell which of the two error possibilities is correct? Any advice? The error is occurring on the "Open" statement.