Use code to define the connect string and include dbAttachSavePWD.

e.g. I use this code to link tables but similar code works with SPT queries.

 Set tdfAccess = dbs.CreateTableDef(rs![LinkTablename], dbAttachSavePWD)
 tdfAccess.Connect = dbsODBC.Connect
---------------------------------------------------------------------------------

I use this procedure to re-create links to SQL Server.
====================================================
For Jet re-linking code see:
http://www.mvps.org/access/tables/tbl0009.htm
====================================================
(This eliminates the need to re-name all the tables to strip out dbo_ and it
allows you to point to different versions of the same database easily.)
There is a local Access table (tblODBCTables) that contains the table names
I want to link to on the Server.
Note: the source table name needs the dbo. prefix which is in the code. The
linked table name usualy omits this. .

Public Sub LinkSQLServerTables(strDSN As String, strDatabase)
On Error GoTo Err_LinkSQLServerTables

Dim dbs As Database, rs As Recordset, tdfAccess As TableDef
Dim dbsODBC As Database, strConnect As String

If strDSN = "" Then
 MsgBox "You must supply a DSN in order to link tables."
 Exit Sub
Else
 strConnect = "ODBC;DSN=" & strDSN & ";UID=User;PWD=password;DATABASE=" &
strDatabase & ";"
End If

SysCmd acSysCmdSetStatus, "Connecting to SQL Server..."

Call DeleteODBCTableNames

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tblODBCTables")
Set dbsODBC = OpenDatabase("", False, False, strConnect)

Do While Not rs.EOF
 Set tdfAccess = dbs.CreateTableDef(rs![LinkTablename], dbAttachSavePWD)
 tdfAccess.Connect = dbsODBC.Connect
 tdfAccess.SourceTableName = dbsODBC.TableDefs("dbo." &
rs![LinkTablename]).Name
 dbs.TableDefs.Append tdfAccess
 rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set dbsODBC = Nothing
Set dbs = Nothing

Exit_LinkSQLServerTables:
   SysCmd acSysCmdClearStatus
   Exit Sub

Err_LinkSQLServerTables:
   MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
   Resume Exit_LinkSQLServerTables

End Sub

'This procedure deletes all linked ODBC table names in an mdb.
Public Sub DeleteODBCTableNames()
On Error GoTo Err_DeleteODBCTableNames

   Dim dbs As Database, tdf As TableDef, I As Integer
   Set dbs = CurrentDb
   For I = dbs.TableDefs.Count - 1 To 0 Step -1
     Set tdf = dbs.TableDefs(I)
     If (tdf.Attributes And dbAttachedODBC) Then
       dbs.TableDefs.Delete (tdf.Name)
     End If
   Next I

   dbs.Close
   Set dbs = Nothing

Exit_DeleteODBCTableNames:
   Exit Sub

Err_DeleteODBCTableNames:
   MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
   Resume Exit_DeleteODBCTableNames

End Sub