Excel connection to SQL Server
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".
Code:
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.
Thanks for the information
Thank You Ken and Aaron.
When I use the connection string from Carl's site, I get the same problem. This means that there is something in the Server's security settings that is causing my error. I knew that was a possibility, I just wasn't sure. Unfortunately, I have to wait for the contractor who has "promised" he will provide me a "secure id and password". I'd much rather this have been my error!
When I saw Aaron's suggestion to the website, I wondered if he'd been there. I had found this website (www.connectionstring.com) via Google, but it always switched me to some site in Sweden (not sure about this, the language was not English). Since I've goofed like this a few times, I followed the link and amazingly, it worked. It also leads to the same type of string.
Thanks guys, I really do appreciate this. The error message essentially says "you goofed or you don't have the right security credentials" and very different actions are necessary depending on which option is the culprit. So it's wait...
At least I feel better.