Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2012

    Unanswered: VBA to SQL Server connection error

    Hi All,

    MS SQL Server 2005
    MS Excel 2003

    I have been getting the following error on the connection string when running some VBA code I wrote to pull data from tables and views I have created in SQL:
    Runtime error -2147467259
    [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

    A sample of the code where the error occurs:
    Dim cnt As ADODB.Connection 
    Dim rst As ADODB.Recordset 
    Dim stSQL As String 
    Dim wbBook As Workbook 
    Dim wsSheet As Worksheet 
    Dim rnStart As Range 
    Dim strBU_Name As String 
    Dim strBU_Code As String 
    strBU_Name = Range("BUSINESS_UNIT").Value 
    strBU_Code = Range("BU_CODE").Value 
    Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _ 
        "Persist Security Info=False;" & _ 
        "Initial Catalog=Training;" & _ 
        "Data Source=wd4bed999d6b0." 
    Set wbBook = ActiveWorkbook 
    Set wsSheet = wbBook.Worksheets("KMV_Main") 
    With wsSheet 
      Set rnStart = .Range("D9") 
    End With 
    stSQL = "SELECT dbo.vw_KMV_Final.Name, '" & _ 
            strBU_Name & "' AS 'Segment', " & _ 
            "dbo.vw_KMV_Final.Region, " & _ 
            "dbo.vw_KMV_Final.[Group Name], " & _ 
            "dbo.vw_KMV_Final.[Linkage between Rated Entity and Group], " & _ 
            "dbo.vw_KMV_Final.Previous, dbo.vw_KMV_Final.[Current], " & _ 
            "dbo.vw_KMV_Final.[Percent Change], " & _ 
            "dbo.vw_KMV_Final.[New CCR from Previous], " & _ 
            "dbo.vw_KMV_Final.[New CCR from Current], " & _ 
            "dbo.vw_KMV_Final.[CCR From EDF Change], " & _ 
            "dbo.vw_KMV_Final.[Indicated Movement], " & _ 
            "dbo.vw_KMV_Final.[Risk Grade Review Event Occurred], " & _ 
            "dbo.vw_KMV_Final.[S&P Rating Ex KMV] , " & _ 
            "dbo.vw_KMV_Final.[Moody's Rating Ex KMV]" & _ 
            " FROM dbo.vw_KMV_Final LEFT OUTER JOIN " & _ 
            "dbo.tbl_KMV_BU_Map ON dbo.vw_KMV_Final.Name = dbo.tbl_KMV_BU_Map.NAME " & _ 
            "WHERE (dbo.tbl_KMV_BU_Map." & strBU_Code & " = 1)" 
    Set cnt = New ADODB.Connection 
    With cnt 
      .CursorLocation = adUseClient 
      .Open stADO 
      .CommandTimeout = 0 
      Set rst = .Execute(stSQL) 
    End With 
    rnStart.CopyFromRecordset rst 
    Set rst = Nothing 
    Set cnt = Nothing 
    The error appears on the line 'cnt.Open stADO'
    The code was executing properly yesterday but now it fails every time I try to run it. I've tried having other people run it on their machines, and the same error occurs. Other programs (created by other people) with no visible differences in the connection string can still connect to the database.

    Please note that my knowledge on connection types and other related matters is fairly poor (i.e. I've read through some solutions involving setting up named pipes but don't really understand how to set that up for my situation)

    Any help would be appreciated, as I've been trying to work through this since yesterday. Thanks in advance!

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    Check your data source in stADO, I'm 99% certain that it is your problem.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2012

    Thanks for the reply! Just for clarification, what exactly would I be looking for when I check my source data? I've gotten the error before when I've tried to access the table through VBA while the source was open on SQL Server, but it occurs even when I have that closed.

  4. #4
    Join Date
    Jan 2003
    Provided Answers: 17
    Is there supposed to be a period in the name of the server in that connection string?

  5. #5
    Join Date
    Jul 2012

    I actually thought that was part of the issue at first, but after some testing, adding or removing the period doesn't really make a difference in the performance of the query or the integrity of the connection.

  6. #6
    Join Date
    Jul 2012


    Just as a heads up to those looking to help out with this problem: the macro has been working these past few days without issue, but I didn't actually make any changes to the code nor did I restart the server where the database is located. However, I would feel much more secure understanding what happened in the first place so that I can put something in there to make sure it doesn't happen to me (or any of my users) in the future.

Posting Permissions

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