Unable to connect to SQL database via Excel VBA

Question

I am unable to connect to my database using VBA:

Sub testSub()
    Dim cn As Object
    Dim cnStr As String
    cnStr = "DRIVER=SQL Server;SERVER=myServerName;Database=myDbName;Trusted_Connection=Yes"
    Set cn = CreateObject("ADODB.Connection")
    With cn
        .ConnectionString = cnStr
        .ConnectionTimeout = 10
        .Open
        If .State = 1 Then
            'rest of code
        End If
    End With
End Sub

the code fails on the .Open line with the error message:

No problems connecting to this database via SSMS. Also, no problems connecting to some other databases using the above VBA. Any idea why it's not allowing me to connect via Excel/VBA and how to fix this, please?

David Browne - Microsoft · Accepted Answer · 2020-04-25 20:12:20Z

3

Use the new Microsoft OLE DB Driver for SQL Server or even the new Microsoft ODBC Driver for SQL Server.

See Using ADO with OLE DB Driver for SQL Server for guidance on updating VB, VBA, and ASP.OLD apps to use the new driver.

edited Apr 25, 2020 at 20:12

answered Apr 25, 2020 at 17:14

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unable to connect to SQL database via Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related