CODEWITHSUNDEEP
c#sqlms-accessms-access-2007oledb
Ben
Ben

Reputation: 2725

changing the sourcetable of a linked table in access 2007 with C#

I'll start by asking am I right in thinking that in the image below: enter image description here

the 'TABLE=CLOASEUCDBA.T_BASIC_POLICY' is not part of the connection string? in fact it is the source table name?

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.

my code for far to do this is:

    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();

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.

EDIT: I have tried to go a different route with no futher success using the 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"; 
        }

In this case I get the error "ODBC--call failed."

Upvotes: 3

Views: 1691

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123584

Since we're not allowed to change the SourceTableName of a TableDef object that already exists in the TableDefs collection we need to create a new TableDef object, .Delete the old one, and then .Append the new one:

// This code requires the following COM reference in your project:
//
//     Microsoft Office 14.0 Access Database Engine Object Library
//
// and the declaration
//
//     using Microsoft.Office.Interop.Access.Dao;
//
// at the top of the class file            

string tableDefName = "CLOASEUCDBA_T_BASIC_POLICY";
var dbe = new DBEngine();
Database db = dbe.OpenDatabase(@"C:\Users\xxxx\Documents\Test.accdb");
var tbdOld = db.TableDefs[tableDefName];
var tbdNew = db.CreateTableDef(tableDefName);
tbdNew.Connect = tbdOld.Connect;
tbdNew.SourceTableName = "CLOASEUCDBA_T_BILLING_INFORMATION";
db.TableDefs.Delete(tableDefName);  // remove the old TableDef ...
db.TableDefs.Append(tbdNew);        // ... and append the new one
db.Close();

Upvotes: 6

Related Questions