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.

[Ken Puls]

I don't know that I'll be able to troubleshoot this with you, but I can share a link with you that might help. Carl Prothman has a huge list of connection strings for OLE databases, which includes at least 5 different connection strings for SQL server, depending on server settings.

You can find the SQL section of his page here.

HTH,

[Oorang]

A few additional resources to look at would be MZ Tools 3.0 (free), which has a connection string building wizard, or www.connectionstrings.com. I have had very good luck with MZ tools, and the ADO string builder is just one of many highly useful features.

Another way to "cheat" and get a connection string is to create a linked table in Access then print/msgbox CurrentDb.TableDefs("MyTable").Connect and it will tell what string to use.

[jwise]

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.

[stanl]

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...

just a S.W.A.G but you might look at the SQL Surface Configuration and check the box to permit OpenRowset() and OpenRecordset().

Stan

[jwise]

Although I haven't posted on this in several days, I have been working on it continuously.

It turns out that I was given the wrong IP address... that made it very difficult. Once this was straightened out, then I got a definite message on the userid/password issue. I used a MsgBox to prevent storing the UID/pw data since I leave all my code open to my users (mostly me now).

Thanks again to all responders.

[Ken Puls]

It turns out that I was given the wrong IP address... that made it very difficult.

I'll bet it did!

I hate it when that kind of thing happens.

[malik641]

The error message looks like:

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

That sucks. I bet you wouldn't have had such a hard time if the error message showed you ONE or THE OTHER, but not both.

[jwise]

Thanks Ken and Joseph.

Although it was far from funny at the time, in retrospect the whole thing is kind of funny. Joseph's point about the error message is precisely the cause of my grief. I would have investigated the IP address much sooner if the message had simply said "No SQL server there."

If I don't remember this next time, then shame on me.

This is a really interesting way to populate a spreadsheet, and it can insure that the data is the latest information available. I am very impressed with this concept.

[Ken Puls]

Although it was far from funny at the time, in retrospect the whole thing is kind of funny. Joseph's point about the error message is precisely the cause of my grief. I would have investigated the IP address much sooner if the message had simply said "No SQL server there."

That's the nature of IT, isn't it? About two months ago, a consultant and I called VMWare support for an issue we had. We had put one of our servers into maintenance mode, so all of the VM's were supposed to automatically move to the other server, but for some reason they just wouldn't. After two hours on the phone with VMWare, the tech finally checked with his boss and found out that our attempt to set a rule to keep two VM's on separate servers blew the moving ability apart. (It makes sense in retrospect.) Our reponse to him (at 2AM) was... "Do you think that maybe we should get a message about this when we set our rules, or maybe a message back from the system telling us why it's stuck?"

I can't count the number of hours I've wasted on something that would have been simple if only for an (effective) error message.