Appending and deleting linked tables in access using c# issue

Question

I have a piece of code that goes through all the linked tables and tables in an access database and for every table(all linked in this case) that matches a certain criteria it should add a new table and delete the old. The new is on a sql server database and the old the oracle, however this is irrelevant. The code is:

var dbe = new DBEngine();
            Database db = dbe.OpenDatabase(@"C:\Users\x339\Documents\Test.accdb");
            foreach (TableDef tbd in db.TableDefs)
            {
                if (tbd.Name.Contains("CLOASEUCDBA_T_"))
                {
                    useddatabases[i] = tbd.Name;
                    string tablename = CLOASTableDictionary[tbd.Name];
                    string tablesourcename = CLOASTableDictionary[tbd.Name].Substring(6);
                    var newtable = db.CreateTableDef(tablename.Trim());
                    newtable.Connect = "ODBC;DSN=sql server copycloas;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=ILFSView;";
                    newtable.SourceTableName = tablesourcename;
                    db.TableDefs.Append(newtable);
                    db.TableDefs.Delete(tbd.Name);
                    i++;
                }

            }
            foreach (TableDef tbd in db.TableDefs)
            {
                Console.WriteLine("After loop "+tbd.Name);

            } 

There are 3 linked tables in this database 'CLOASEUCDBA_T_AGENT', 'CLOASEUCDBA_T_CLIENT' and 'CLOASEUCDBA_T_BASIC_POLICY'. The issue with the code is that it updates the first two tables perfectly but for some unknown reason, it never finds the third. Then in the second loop, it prints it out... it seems to just skip over 'CLOASEUCDBA_T_BASIC_POLICY'. I really dont know why. The weird thing is then that if run the code again, it will change 'CLOASEUCDBA_T_BASIC_POLICY'. Any help would be greatly appreciated.

Answer 1

Modifying a collection while you are iterating over it can sometimes mess things up. Try using a slightly different approach:

1. Iterate over the TableDefs collection and build a List (or perhaps a Dictionary) of the items you need to change. Then,

2. Iterate over the List and update the items in the TableDefs collection.