Access 2013 SQL login error to ODBC SQL database

You need to create a local Access table (called ODBC connections for instance) that lists all the tables in SQL Server (TableName_SQLServer), and the name you want them to appear as in Access (TableName_Access). Then I set a constant being the connection string. Such as
Const strConnect = "ODBC;Driver={SQL Server Native Client 11.0};Server=ServerName;Database=DatabaseName;UID=UserName;PWD=password"

or if you are using SQL Server authentication

Const strConnect = "ODBC;Driver={SQL Server Native Client 11.0};Server=ServerName;Database=DatabaseName;

I ten use this code to connect - just run it once the connection string is set.

Public Function RelinkSQLTables()
On Error GoTo EH

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strTblServer As String
Dim strTblLocal As String
Dim rsTables As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb()

sSQL = "SELECT * FROM ODBCTables where Not IsNull(TableName_SQLServer);"

Set rsTables = db.OpenRecordset(sSQL, dbOpenSnapshot)
Do Until rsTables.EOF
    For Each tdf In db.TableDefs
        If tdf.Name = rsTables!TableName_Access Then
            ''Build the dsn-less connection string
            db.TableDefs.Delete rsTables!TableName_Access
            Exit For
        End If
    Next
        Set tdf = db.CreateTableDef(rsTables!TableName_Access, dbAttachSavePWD, rsTables!TableName_SQLServer, strConnect)
        db.TableDefs.Append tdf
    rsTables.MoveNext
Loop

MsgBox "All SQL tables relinked", vbOKOnly + vbInformation, "Process Complete"

Exit_Sub:
    Exit Function
   
EH:
    MsgBox Err.Number & ": " & Err.Description
   
    Resume Exit_Sub

End Function

Kelvin