DSN-less connections in Microsoft Access - making the linked table connections permanent

If you've read Doug Steele's write-up to enable DSN-less connections in Access, it works great with Trusted Connections.  But what if you're using SQL Server authentication instead, and don't want to store the password or credentials in the code?


You can set the DB_ATTACHSAVEPWD property like such (see Doug's article for the full code):

tdfCurrent.Connect = "ODBC;DRIVER={sql server};DATABASE=" & _
                        DatabaseName & ";SERVER=" & ServerName & _
                        ";Uid=" & myUserName & _
                        ";Pwd=" & myPassword & ";"

tdfCurrent.Attributes = DB_ATTACHSAVEPWD

Then go ahead and run the macros or forms to use the table(s) in question.  Doug states that the username and password will be stored in plaintext in the Connect property, but if you run the FixConnections sub from a debug window, and then run a macro or form, this should save the connection for re-use in the future without having to run FixConnections again.  You should be able to then distribute the MDB file without your users needing to set up a DSN.  But obviously, be aware that anyone with file access to the MDB will be able to insert/update/delete records without any prior authentication!  This should only go on a local disk or well-protected network share.