Create DSN for SQL Server with Username and Password

[cliv]

"Trusted_Connection" is not a valid keyword for SQLConfigDataSource in some driver versions—the DSN does not store the authentication method here. Remove the "Trusted_Connection" line entirely and pass the UID/PWD only at connect time (in TestDSNConnection), not in the DSN attributes.
SQLConfigDataSource does not store the password in the DSN (credentials aren't persisted by design), so you supply them at connect time.

Option Explicit

'Constant Declaration
Private Const ODBC_ADD_DSN = 1
Private Const ODBC_CONFIG_DSN = 2
Private Const ODBC_REMOVE_DSN = 3
Private Const vbAPINull As Long = 0

#If Win32 Then
  Private Declare Function SQLConfigDataSource Lib "odbccp32.dll" _
      (ByVal hwndParent As Long, ByVal fRequest As Long, _
      ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long
#Else
  Private Declare Function SQLConfigDataSource Lib "ODBCINST.DLL" _
      (ByVal hwndParent As Integer, ByVal fRequest As Integer, ByVal _
      lpszDriver As String, ByVal lpszAttributes As String) As Integer
#End If

Private Declare Function SQLInstallerError Lib "odbccp32.dll" _
    (ByVal iError As Integer, pfErrorCode As Long, _
     ByVal lpszErrorMsg As String, ByVal cbErrorMsgMax As Integer, _
     pcbErrorMsg As Integer) As Integer
     
Private Declare Function SQLGetPrivateProfileString Lib "odbccp32.dll" _
    (ByVal lpszSection As String, ByVal lpszEntry As String, _
     ByVal lpszDefault As String, ByVal RetBuffer As String, _
     ByVal cbRetBuffer As Long, ByVal lpszFilename As String) As Long

Private Function DSNExists(ByVal dsnName As String) As Boolean
  Dim sBuf As String * 256
  Dim nRet As Long
  ' Read the list of DSNs in the "ODBC Data Sources" section
  nRet = SQLGetPrivateProfileString("ODBC Data Sources", dsnName, "", _
                                    sBuf, 256, "ODBC.INI")
  DSNExists = (nRet > 0)
End Function

Private Sub ShowODBCError()
  Dim sMsg As String * 512
  Dim lErr As Long
  Dim iLen As Integer
  Dim r As Integer
  r = SQLInstallerError(1, lErr, sMsg, 512, iLen)
  If iLen > 0 Then
    MsgBox "ODBC error " & lErr & ": " & Left$(sMsg, iLen)
  Else
    MsgBox "No ODBC installer error info"
  End If
End Sub

'//Add DSN
Private Sub Command1_Click()
  Const DSN_NAME = "DSN_TEMP"

  If DSNExists(DSN_NAME) Then
    Dim r As VbMsgBoxResult
    r = MsgBox(DSN_NAME & " already exists. I'm recreating it.?", vbYesNo + vbQuestion)
    If r = vbNo Then Exit Sub
    ' Optional: Wipe it down first
    Call SQLConfigDataSource(vbAPINull, ODBC_REMOVE_DSN, _
                             "SQL Server", "DSN=" & DSN_NAME & Chr$(0) & Chr$(0))
  End If
  
  #If Win32 Then
    Dim intRet As Long
  #Else
    Dim intRet As Integer
  #End If
  Dim strDriver As String
  Dim strAttributes As String
  Dim strUID As String
  Dim strPWD As String

  strUID = "user"               ' supplied username
  strPWD = "pass"               ' supplied password

  strDriver = "SQL Server"
  strAttributes = "SERVER=SERVER\ACE" & Chr$(0)
  strAttributes = strAttributes & "DESCRIPTION=Temporary SQLServer DSN" & Chr$(0)
  strAttributes = strAttributes & "DSN=DSN_TEMP" & Chr$(0)
  strAttributes = strAttributes & "DATABASE=TermEX" & Chr$(0)
  strAttributes = strAttributes & Chr$(0)

  intRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, _
      strDriver, strAttributes)
  If intRet Then
    MsgBox "DSN Created"
    Call TestDSNConnection("DSN_TEMP", strUID, strPWD)
  Else
    MsgBox "Create Failed"
    Call ShowODBCError
  End If
End Sub

Private Sub TestDSNConnection(ByVal dsnName As String, _
                              ByVal uid As String, ByVal pwd As String)
  Dim cn As ADODB.Connection
  Set cn = New ADODB.Connection
  On Error GoTo ConnErr
  cn.Open "DSN=" & dsnName & ";UID=" & uid & ";PWD=" & pwd & ";"
  MsgBox "Connection OK"
  cn.Close
  Set cn = Nothing
  Exit Sub
ConnErr:
  MsgBox "Connection failed: " & Err.Description
End Sub

1. UID/PWD written into the attribute string only set defaults that the DSN may prompt for later — they are not saved in the registry as the password, so always pass UID/PWD again at cn.Open.
2. If you're on a new SQL Server, consider strDriver = "ODBC Driver 17 for SQL Server" (or 18) instead of the legacy "SQL Server"
3. First check if DSN exist in ODBC32 and maybe delete