password in ms access front end app to sql gets erased 1

[JacobTechy]

I have a ms access application 2003 that is the front end to a sql database 2000. The database is in a remote location. Well each time the programmer sends me a new copy with changes I have to use the get external data and re link the tables using odbc because the password gets erased. Is there an easier way to save or hard code the password by using a host script or something to that effect so I can just copy and paste the updated access app to update it rather then going thru the relink process.

 

[Remou]

You can link tables with code. Here is an example with an Access connect string:

  strConnect = "MS Access;PWD=PWD;DATABASE=" &  strFullPathToDB
    End If

'Create new linked table   
   Set tdf = db.CreateTableDef("tablename", dbAttachSavePWD, _
                "tablename", strConnect)
   db.TableDefs.Append tdf

'Relink table
   db.TableDefs("tablename").Connect = strConnect

   db.TableDefs("tablename").RefreshLink

You can get a suitable connection string here:

http://www.connectionstrings.com/

This may be helpful:

http://www.mvps.org/access/tables/tbl0009.htm