CODEWITHSUNDEEP
sql-servervba
tsdn
tsdn

Reputation: 427

Unable to connect to SQL database via Excel VBA

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:

enter image description here

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?

Upvotes: 0

Views: 808

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88852

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.

Upvotes: 3

Related Questions