I compared the value of the table Attributes (a long integer) & discovered that my new table was different. A quick trip to Access Help was truly helpful - I was missing the attribute dbAttachSavePWD so basically here is what worked (the fix is in bold):
Sub SQL_AttachOne(TableName As String)
Dim db As DAO.Database, tdf As DAO.TableDef
Const s = "the connection string goes here"
Set db = CurrentDb
Set tdf = db.CreateTableDef
.Name = TableName
.SourceTableName = TableName
.Connect = s
.Attributes = .Attributes Or dbAttachSavePWD
The Or operator sets the appropriate flag for Access to save the info.