title: changing the sourcetable of a linked table in access 2007 with C#
body: <p>I'll start by asking am I right in thinking that in the image below:
  <img src="https://i.stack.imgur.com/nsjOC.png" alt="enter image description here"></p>
  
  <p>the 'TABLE=CLOASEUCDBA.T_BASIC_POLICY' is not part of the connection string? in fact it is the source table name?</p>
  
  <p>I'm looking to alter this to another linked table on the same database. The connection string should there be the same and the name that appears in ACCESS should be the same. The only difference should be under the hood it is actually referencing another table and of course if you open the table it will contain different fields and data.</p>
  
  <p>my code for far to do this is:</p>
  
  <pre><code>    var dbe = new DBEngine();
      Database db = dbe.OpenDatabase(@"C:\Users\xxxx\Documents\Test.accdb");
      foreach (TableDef tbd in db.TableDefs)
      {
  
          if (tbd.Name.Contains("CLOASEUCDBA_T_BASIC_POLICY"))
          {
              tbd.SourceTableName = "CLOASEUCDBA_T_BILLING_INFORMATION"; 
          }
      }
      db.Close();
  </code></pre>
  
  <p>However I'm getting a big fat COMException "Cannot set this property once the object is part of a collection.". I'm not sure exactly why and all the examples I can find online are all written in VB/VBA and I only have very very limited exposure to this. Any help is appreciated.</p>
  
  <p>EDIT:
  I have tried to go a different route with no futher success using the code:</p>
  
  <pre><code>        if (tbd.Name.Contains("CLOASEUCDBA_T_BASIC_POLICY"))
          {
              var newtable = db.CreateTableDef("this is a new table");
              newtable.Name = "new table";
              newtable.Connect = tbd.Connect;
              newtable.SourceTableName = "CLOASEUCDBA_T_BILLING_INFORMATION";
              db.TableDefs.Append(newtable);
              //tbd.SourceTableName = "CLOASEUCDBA_T_BILLING_INFORMATION"; 
          }
  </code></pre>
  
  <p>In this case I get the error "ODBC--call failed."</p>