title: Linking tables through vba
body: <p>I am completing an task that has been assigned to me for my colleagues in the USA (I'm based in the UK). However my database application uses linked Tables to a microsoft access database file over a network that has been encrypted as it stores customer information. </p>
  
  <p>There is no colleague on the USA side with a similar skill set to make any alterations to database locations through VBA. I have seen various methods to connect to SQL databases as shown on the microsoft link below. However, to make it easier for someone to alter the location of the database. </p>
  
  <p>Is it possible to ammend the below code so that will look at a text file which will house the location of the database back end then (C:\users\public\test1) for example and then Link the tables to the front end. </p>
  
  <p>I have found the code below but it errors out saying that "object msysaccessstorage already exists". It errors out on the line "CurrentDb.TableDefs.Append tdf".</p>
  
  <pre><code>Option Explicit
  Dim dbs As DAO.Database
  Dim tdf As DAO.TableDef
  Dim mypass As String
  Dim mypath As String
  Dim myDb As String
  Dim TableName As String
  
  
  Function connectme()
  
  mypass = "test1"
  mypath = "C:\Users\Test1\Desktop\"
  myDb = "EM1.accdb"
  
   ' Delete links so there won't be any duplicates
  For Each tdf In CurrentDb.TableDefs
  If Left(tdf.Name, 4) &lt;&gt; "MSys" And Left(tdf.Name, 15) &lt;&gt; "tblReportsState" And _
  (tdf.Attributes And dbAttachedTable) = dbAttachedTable Then
  CurrentDb.TableDefs.Delete tdf.Name
  End If
  Next tdf
  Set tdf = Nothing
  
   ' Setup Links
  Set dbs = OpenDatabase(mypath &amp; myDb, False, False, "MS Access;PWD=" &amp; mypass)
  
  For Each tdf In dbs.TableDefs
  If Left(tdf.Name, 4) &lt;&gt; "msys" Then
  TableName = tdf.Name
  Set tdf = CurrentDb.CreateTableDef(TableName)
  tdf.Connect = ";PWD=" &amp; mypass &amp; ";Database=" + mypath + myDb
  tdf.SourceTableName = TableName
  CurrentDb.TableDefs.Append tdf
  End If
  Next
  
  End Function
  </code></pre>