none
ADO Connection Will Not Open RRS feed

  • Question

  • Office 2013 Pro, Excel

    VBA Project References: 

    • Microsoft ActiveX Data Objects (Multi-dimensional) 6.0 Library
    • Microsoft ActiveX Data Objects 6.1 Library
    • Microsoft ActiveX Data Objects Recordset 6.0 Library

    SQL Server 2008

    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
            
    cnn.ConnectionString = "Server=servername.domain.com;Database=databasename;Trusted_Connection=True;"
    cnn.Open

    This is all pretty standard and I've never had issues before, but the connection won't open.  No errors and the server/database and trusted credentials are all good (same I as use for SQL Management Studio).

    I tried MDAC 2.8, but that didn't resolve the issue.

    Thanks in advance.

    Tuesday, May 16, 2017 8:34 PM

Answers

  • Hello,

    I think the connection string is wrong. You don't specify the provider.

    You could use the following connection string to create a trusted connection to SQL Server.

     cnn.ConnectionString = "Provider=SQLOLEDB;Server=ServerName;Initial Catalog=DBName;Integrated Security=SSPI;"

    Or

    cnn.ConnectionString = "Driver={SQL Server};Server=ServerName;Database=DBName;Trusted_Connection=Yes;"

    You could test the following code to check if the connection is opened.

    Sub test()
    Dim cnn As ADODB.Connection
     Set cnn = New ADODB.Connection      
     cnn.ConnectionString = "Provider=SQLOLEDB;Server=ServerName;Initial Catalog=DBName;Integrated Security=SSPI;"
     '"Driver={SQL Server};Server=ServerName;Database=DBName;Trusted_Connection=Yes;"
     cnn.Open
     If cnn.State = adStateOpen Then
          MsgBox "Success"
       Else
          MsgBox "Fail"
       End If
       cnn.Close
    End Sub

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 18, 2017 7:13 AM
    Moderator

All replies

  • Hi,

    I notice that you are using VBA for the SQL server connection. For further troubleshooting, I would move the thread to Excel for developers forum for more help:

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

    They are more familiar with VBA codes. Hope you can find the solution there.


    Regards,

    Winnie Liang


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Wednesday, May 17, 2017 3:17 AM
  • Hello,

    I think the connection string is wrong. You don't specify the provider.

    You could use the following connection string to create a trusted connection to SQL Server.

     cnn.ConnectionString = "Provider=SQLOLEDB;Server=ServerName;Initial Catalog=DBName;Integrated Security=SSPI;"

    Or

    cnn.ConnectionString = "Driver={SQL Server};Server=ServerName;Database=DBName;Trusted_Connection=Yes;"

    You could test the following code to check if the connection is opened.

    Sub test()
    Dim cnn As ADODB.Connection
     Set cnn = New ADODB.Connection      
     cnn.ConnectionString = "Provider=SQLOLEDB;Server=ServerName;Initial Catalog=DBName;Integrated Security=SSPI;"
     '"Driver={SQL Server};Server=ServerName;Database=DBName;Trusted_Connection=Yes;"
     cnn.Open
     If cnn.State = adStateOpen Then
          MsgBox "Success"
       Else
          MsgBox "Fail"
       End If
       cnn.Close
    End Sub

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 18, 2017 7:13 AM
    Moderator