Access Database File not closing while opening it in c# programmatically

Question

I am new to this. In fact this is the first time I am ever posting a question on stackoverflow. I have done many searches but yet could not find an answer to this. Here is a gist of what I am trying to do.

1. Open an access database file using ADOX.
2. For all non-hidden and non-system tables in database, add an entry to the "validation text" property.
3. Close the access database file.

Here is what is happening with the file.

1. The lock file *.ldb is present in the folder and this triggers the error that the database is open by another process.

Here is what I have tried in vain.

1. Close the table opened at each iteration of the foreach loop.
2. Close the catalog class and the object immediately after the foreach loop.
3. Remove all code inside the foreach loop.

Here is what I am planning to do.

1. Use a Try Catch to catch the error.

       cn = new ADODB.Connection();
       cat = new ADOX.CatalogClass();
       cn.Open(tmpStr);
       cat.ActiveConnection = cn;
   
       //Loop through all tables to add the validation text
       foreach (Table t in cat.Tables)
       {
   
   
           tname = t.Name;
           ttype = t.Type == null ? string.Empty : t.Type;
           tprop = t.Properties["Jet OLEDB:Table Hidden In Access"].Value.ToString();
           if (ttype.ToUpper() == "TABLE" && tprop.ToUpper() == "FALSE") // 
           {
               t.Properties["Jet OLEDB:Table Validation Text"].Value = "this is table " + tname + "                                                                                                student1";
           }
   
           //System.Runtime.InteropServices.Marshal.FinalReleaseComObject(t);
   
       }
       //Close all open objects
   
       System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cat.Tables);
       System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cat.ActiveConnection);
       System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cat);
       GC.Collect();
   

Do you think there is something wrong with the way I am trying to accomplish what I would like to do? Thank you and have a wonderful day.

Answer 1

I have long time without working with ADODB, but according to what I remember: your should close the connection, the GC.Collect() will "eventually" collect the orphan objects but I believe you should use the Close method of the Connection object. https://msdn.microsoft.com/en-us/library/ms807027.aspx