Consulting

Results 1 to 10 of 10

Thread: Solved: Excel connection to SQL Server

  1. #1

    Solved: 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".
    [vba] 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 [/vba]

    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.

  2. #2
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    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,
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  3. #3
    Knowledge Base Approver VBAX Master Oorang's Avatar
    Joined
    Jan 2007
    Posts
    1,135
    Location
    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.
    Cordially,
    Aaron



    Keep Our Board Clean!
    • Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
    • Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.

  4. #4

    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.

  5. #5
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Quote Originally Posted by jwise
    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

  6. #6

    Thanks Stan

    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.

  7. #7
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Quote Originally Posted by jwise
    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.
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  8. #8
    Administrator
    2nd VP-Knowledge Base
    VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    Quote Originally Posted by jwise
    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.




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  9. #9

    A sense of humor

    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.

  10. #10
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Quote Originally Posted by jwise
    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.
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •