Compact and repair Access DataBase in run time using C# without Access installed

0

I write a program that should run on a lot of machines

I did not installed Access in the machines But I have an access database using on them

When I write and delete data from this Access databases its size grow up so every few days I have to compacts them and to shrink the size

This is the code I using

public void Compacting()
{
    try
    {
        Microsoft.Office.Interop.Access.Application application = new Microsoft.Office.Interop.Access.Application();

        string dbName = "";
        try
        {
            dbName = ConfigurationManager.ConnectionStrings["LocalPulserDB"].ConnectionString.Split("Data Source=").Last().Split(";").First();
        }
        catch (Exception ex)
        {
            string localDbError = "DataBase location is incorrect ";
            System.Windows.MessageBox.Show(localDbError);
            Environment.Exit(1);
        }

        CompactAndRepair(dbName, application);
    }
    catch (Exception ex)
    {
        try
        {
            LocalPulserDBManagerInstance.WriteLog(ex.StackTrace, ex.Message);
        }
        catch (Exception)
        {

        }
    }
}

private void CompactAndRepair(string accessFile, Microsoft.Office.Interop.Access.Application app)
{
    string tempFile = Path.Combine(Path.GetDirectoryName(accessFile),
                      Path.GetRandomFileName() + Path.GetExtension(accessFile));

    app.CompactRepair(accessFile, tempFile, true);
    app.Visible = false;

    FileInfo temp = new FileInfo(tempFile);
    temp.CopyTo(accessFile, true);
    temp.Delete();
}

But I got the next error :

{"Retrieving the COM class factory for component with CLSID {73A4C9C1-D68D-11D0-98BF-00A0C90DC8D9} failed due to the following error: 80080005 Server execution failed (Exception from HRESULT: 0x80080005 (CO_E_SERVER_EXEC_FAILURE))."}

What can I do?

c#
ms-access
compact-database
asked on Stack Overflow Jan 9, 2020 by hedbisker • edited Jan 9, 2020 by Sushant Yelpale

2 Answers

1

Interop requires Office to be installed on the machine the code is running on, I suspect you won't be able to utilize that method for performing the compact and repair.

https://social.msdn.microsoft.com/Forums/en-US/973ce94f-5235-4be9-a2b1-51ba7d35e1f3/installing-office-primary-interop-assemblies-without-actually-installing-office?forum=innovateonoffice

You may however be able to utilize this standalone tool, "Jet compact utility": https://docs.microsoft.com/en-us/office/troubleshoot/access/jet-compact-utility-download

answered on Stack Overflow Jan 9, 2020 by Blaise
0

You do not need nor even want to create a whole instance of access to just compact. (or even use + pull data from the tables.

Set a reference to the ACE database engine. That way you can pull + use data, and not have to automatic (create) a rather heavy application object like Access. And if you do create a WHOLE instance of Access, then things like VBA code and startup forms run (not only is a that bad since now you have a hidden UI - but you can't answer prompts, and worse that startup code may will have launched a form which in term will open a table, and then you can't compact until such time all tables are closed.

IN vb.net code, this will compact:

    Dim ACEEngine As New Microsoft.Office.Interop.Access.Dao.DBEngine

    Dim strDatabase As String = "c:\test\test44.accdb"

    ' you always compact to a copy - you cannot compact "over" the existing database

    Dim strTempDB As String = "c:\test\temp.accdb"
    Debug.Print("starting compact")
    Try
        ACEEngine.CompactDatabase(strDatabase, strTempDB)
        Debug.Print("compact done")

    Catch ex As Exception
        Debug.Print(Err.Description)
    End Try

Note that: You can check the error code as per above, since if ANY other users are in the database, then the compact will NOT occur.

The above output if a someone else has the database open?

starting compact
Exception thrown: 'System.Runtime.InteropServices.COMException' in ACETest.exe
You attempted to open a database that is already opened by user 'Admin' on machine
'ALBERTKALLAL-PC'. Try again when the database is available.

So, catch the error. As above shows, you need a interop reference to

C:\Program Files (x86)\Microsoft Visual Studio 12.0\
Visual Studio Tools for Office\PIA\Office14\
Microsoft.Office.interop.access.dao.dll

Just check that err.Number is 0. If the compact occurred, then you can delete or better yet rename the original database, and then rename the "temp" database back to the original name. (A compact + repair from the Access UI does this behind the scenes and actually never overwrites the original file during the compact process as it might fail or not work). You could attempt to check for existing .ldb file (a lock file) before you attempt the compact, but the compact process checks + requires that you have 100% exclusive use of the file, and if the compact can't get exclusive use of the file, then a error message is spit out as per above.

So, just add the assembly reference to your project. You should also force your .net project to x86, and not leave it to "ANY CPU".

the above will work WITHOUT Access having been installed. However, you will need to have the JET or ACE data engine installed. The JET engine is installed on all copies of windows by default - but will ONLY work with mdb files. If you need to compact and use a accDB file, then you will have to install the ACE database engine - but it is a FAR smaller footprint and install then is a whole copy of Access or the run time (both are about the same size and are quite large. As noted, with above, then you don't require Access to have been installed on the computer.

Edit

You can find + install a version of ACE from here: https://www.microsoft.com/en-us/download/details.aspx?id=54920

Note in above, you are given two choices. (x32 or x64 - so you have to install the version you want. This also suggests you need two versions of your .net code OR YOU CAN find a shortcut way to launch "any" cpu to start as x32 or x64 - HOW you start your .net project will determine if it is running as x32 or x64 if you going to choose "any cpu" as opposed to forcing the .net project (and providing two versions of the project as I have done in the sample link below).

I have a working .net (tiny) .exe file from here that lets you run as x32, or x64, and thus you can quick check which version of ACE you have installed.

link: https://onedrive.live.com/embed?cid=B18A57CB5F6AF0FA&resid=B18A57CB5F6AF0FA%21101313&authkey=AB-I3mHVZhLmd2Q

answered on Stack Overflow Jan 10, 2020 by Albert D. Kallal • edited Jun 20, 2020 by Community

User contributions licensed under CC BY-SA 3.0