SQL Server DSN-less connection string error in MS Access: Data source name not found and no default driver specified

Question

I have this DSN-less Connection String in MS Access which I want to use to connect to SQL Server 2019:

Private Const CONNECTION_STRING = "ODBC;Driver={SQL Server Native Client 11.0};Server=aaa;Database=bbb;Trusted_Connection=yes;"

When I use this string in this code below, it works and correctly retrieves the linked tables:

Public Sub LinkSQLTables()
On Error GoTo ErrHandler

    Dim td As TableDef
    Dim rs As Recordset
    
    
    For Each td In CurrentDb.TableDefs
    
        If td.Name Like "v_*" Or td.Name Like "vDE_*" Then
            CurrentDb.TableDefs.Delete td.Name
        End If
        
    Next
    
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM SQL_Links", dbOpenSnapshot)
    
    Do While Not (rs.BOF Or rs.EOF)
    
        Set td = CurrentDb.CreateTableDef(rs!LinkName, dbAttachSavePWD, rs!LinkName, CONNECTION_STRING)
        CurrentDb.TableDefs.Append td

        CurrentDb.Execute "CREATE INDEX " & rs("LinkName") & "_PK ON " & rs("LinkName") & " (" & rs("PrimaryKeyField") & ") WITH PRIMARY"
        
        rs.MoveNext
    
    Loop
    
    rs.Close
    Set rs = Nothing
        
Exit Sub
ErrHandler:

    MsgBox "Během pokusu o spojení s databází nastala chyba. Aplikace bude ukončena." & vbNewLine & vbNewLine & _
            "Číslo chyby: " & Err.Number & vbNewLine & _
            "Popis chyby: " & Err.Description, vbCritical + vbOKOnly, "Chyba"
            
    rs.Close
    Set rs = Nothing
            
    Application.Quit
    
End Sub

However, when I use the same connection string to open an ADO connection as seen in the code below:

Set Conn = New ADODB.Connection
Conn.ConnectionString = CONNECTION_STRING
Conn.Open

I get an error on Conn.Open: Data source name not found and no default driver specified.

What should I do to correct this? Thanks.

Answer 1

ODBC; is a DAO-specific prefix. When working with ADO, you must omit that.

I often use something like this:

Const CONNECTION_STRING_DAO = "ODBC;Driver={SQL Server Native Client 11.0};Server=aaa;Database=bbb;Trusted_Connection=yes;"
Const CONNECTION_STRING_ADO = "Driver={SQL Server Native Client 11.0};Server=aaa;Database=bbb;Trusted_Connection=yes;" 

Then, use the appropriate string for the appropriate technique.

Quite often, I use OLEDB for my ADO strings, since ADO natively works with OLEDB, and DAO does not work with OLEDB at all.