DAO 3.6 - Repair and Compact Database

There is something I came across a while back when migrating an old VB6 application to .NET. Everything was fine but had some roadblocks with DAO in VB6. Typically in VB6 all the data objects are created using DAO and migrating it to ADO.NET is simple except one area. There is a functionality of RepairDatabase which is not present in the ADO.NET. I left that as it is and to do that imported Interop.DAO.dll (3.51 version) in new .NET application. RepairDatabase method is not present in DAO 3.6 onward, will discuss it later.

The issue faced, even though it is working well in development environment but in production environment it is throwing me error.
System.Runtime.InteropServices.COMException (0x80040112): Creating an instance of the COM component with CLSID {00000010-0000-0010-8000-00AA006D2EA4} from the IClassFactory failed due to the following error: 80040112.

Googled and got confused, nothing specific and nothing conclusive. So, started gathering information from different sources and tried to get to some conclusion...

What is DAO?
(Data Access Objects) was the first object-oriented interface that exposed the Microsoft Jet database engine (used by Microsoft Access) and allowed Visual Basic developers to directly connect to Access tables - as well as other databases - through ODBC. DAO is suited best for either single-system applications or for small, local deployments. Here is a good post to help you out.

Reason for this error is Data Access Objects (DAO) is not properly registered. But when you try to register using regsvr32, DAO 3.51 it fails as there is no entry point. but thats not an issue with DAO 3.6. There are some major difference between 3.5 and 3.6. The later has been totally revamped, this is by design to match Microsoft Jet 4.0.

As per the Microsoft recommendation
RDO and ADO can still be used in code from Visual Basic 2008, with some trivial modifications. However, Visual Basic 2008 does not support DAO and RDO data binding to controls, data controls, or RDO User connection. We recommend that if your applications contain DAO or RDO data binding you either leave them in Visual Basic 6.0 or upgrade the DAO or RDO data binding to ADO before upgrading your project to Visual Basic 2008, as ADO data binding is supported in Windows Forms. Information on how to upgrade DAO or RDO to ADO in Visual Basic 6.0 is available in the Visual Basic 6.0 Help.

Here is a surprise...
In Data Access Object (DAO) 3.6, the RepairDatabase method is no longer available or supported. This is by design to match Microsoft Jet 4.0. If you need this functionality, you can use the CompactDatabase method, which also repairs a Microsoft Jet database. Registering DAO 3.6 dll has no issue. Compacting a Jet/ACE database first detects if there are any problems in need of repair and if there are none, it skips the repair step and just compacts the file (rewriting data and index pages in contiguous data files and discarding unused data pages and updating all statistics and internal pointers, etc.).

In JRO if you compact a Access 97 database it will convert to Access 2000 format because even though JRO can read the Access 97 file it no longer support it and it is by design.

Following research implementation has two sections one Repairing and Compacting database using DAO and JRO. You may need to include the DAO 3.6 dlls in your application folder.
For that Add Following COM reference:

• Microsoft DAO 3.6 Object Library
• Microsoft Jet and Replication Objects 2.6 Library

namespace RepairDB
{
    class Program
    {
        static void Main(string[] args)
        {
            
            try
            {
                DAO.Database dd;
                DAO.DBEngine db = new DAO.DBEngine();
                dd = db.OpenDatabase(@"d:\KSDB1.mdb", null, null, ";pwd=KSTEST1");
                dd.Close();
                
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            Console.WriteLine("Open and Close of database successful using DAO.");
            try
            {
                File.Delete(@"d:\KSDB1_Tmp.mdb");
                if (File.Exists(@"d:\KSDB1.mdb"))
                {
                    DAO.DBEngine db = new DAO.DBEngine();
                    db.CompactDatabase(@"d:\KSDB1.mdb", @"d:\KSDB1_Tmp.mdb", null, null, ";pwd=KSTEST1");
                    File.Delete(@"d:\KSDB1.mdb");
                    File.Move(@"d:\KSDB1_Tmp.mdb", @"d:\KSDB1.mdb");
                }   
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            Console.WriteLine("Compacting and Repair database successful using DAO.");
            try
            {
                File.Delete(@"d:\KSDB1_Tmp.mdb");
                if (File.Exists(@"d:\KSDB1.mdb"))
                {
                    JRO.JetEngine jj = new JRO.JetEngine();
                    jj.CompactDatabase(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\KSDB1.mdb;Jet OLEDB:Database Password=KSTEST1",
                        @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\KSDB1_Tmp.mdb;Jet OLEDB:Database Password=KSTEST1");
                    File.Delete(@"d:\KSDB1.mdb");
                    File.Move(@"d:\KSDB1_Tmp.mdb", @"d:\KSDB1.mdb");
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            Console.WriteLine("Compacting and Repair database successful using JRO.");
            Console.ReadLine();
        }
    }
}

Furthur Readings
http://msdn.microsoft.com/en-us/library/office/aa164825(v=office.10).aspx
http://msdn.microsoft.com/en-us/library/aa984815(v=vs.71).aspx
http://msdn.microsoft.com/en-us/library/e80y5yhx(v=vs.110).aspx