title: Changing a tabledef .connect property in VBA
body: <p>I am trying to do something that I have done a hundred times, but it suddenly won't work.  </p>
  
  <pre><code>acc.AutomationSecurity = msoAutomationSecurityLow   'Remove Security Prompt
  acc.OpenCurrentDatabase path &amp; "Year " &amp; Range("yr") &amp; "\" &amp; Range("yr") &amp; Range("mo") &amp; "\Year " &amp; Range("yr") &amp; Range("mo") &amp; " GENERIC DB NAME.mdb", True
  
  'update link
  acc.CurrentDb.TableDefs("TABLE NAME").Connect = "MS Access;DATABASE=" &amp; path &amp; "Year " &amp; Range("yr") &amp; "\" &amp; Range("yr") &amp; Range("mo") &amp; "\Year " &amp; Range("yr") &amp; Range("mo") &amp; " OTHER DB.mdb"
  acc.CurrentDb.TableDefs("TABLE NAME").RefreshLink
  </code></pre>
  
  <p>I don't know if this is related to the fact that we recently updated to Excel 2010 and the db is still ACCESS 2003 but this should be a simple bit of code to run.  Nothing happens when I run the above code.  The file opens the right way but the connection string never gets assigned to the table def object, ie. It runs without erroring, but the table is not linking to the new database.  I have found the following code does work and have used it to get around the issue.  I am still curious why I can't assign a value to the .connect property in the style 'application.currentdb.tabledef("TABLE").connect', but if I assign the currentdb to a new database object I can.  </p>
  
  <p>So I don't know why but if I use this it works</p>
  
  <pre><code>dim db as DAO.database
  set db = acc.CurrentDb
  db.TableDefs("TABLE NAME").Connect = "MS Access;DATABASE=" &amp; path &amp; "Year " &amp; Range("yr") &amp; "\" &amp; Range("yr") &amp; Range("mo") &amp; "\Year " &amp; Range("yr") &amp; Range("mo") &amp; " OTHER DB.mdb" 
  db.TableDefs("TABLE NAME").RefreshLink 
  </code></pre>
  
  <p>Thanks for any help you can provide.</p>