Compact access database

David Smith

16y15.8k.NET

     Hi i'm compacting access database for some reason when i get to the invoke method below via the strConnection, it does not understand the |DataDirectory|\Database.mdb path
    but if say "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\Database.mdb" it will find the database, right now with connection string below the error says not a valid file name

    string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Database.mdb";

     public static void CompactAccessDB(string connectionString, string mdwfilename)
    {
      object[] oParams;
      //create an inctance of a Jet Replication Object

      object objJRO =
        Activator.CreateInstance(Type.GetTypeFromProgID("JRO.JetEngine"));

      //filling Parameters array
      oParams = new object[] {
                                connectionString,   //Database location you compacting
                                "Provider=Microsoft.Jet.OLEDB.4.0;Data" +
                                " Source=C:\\Temp\\Database.mdb;Jet OLEDB:Engine Type=5" //location you are moving the database to
                             };

      //invoke a CompactDatabase method of a JRO object pass Parameters array
      objJRO.GetType().InvokeMember("CompactDatabase",
                                    System.Reflection.BindingFlags.InvokeMethod,
                                    null,
                                    objJRO,
                                    oParams);

      System.IO.File.Delete(mdwfilename);
      System.IO.File.Move("C:\\Database.mdb", mdwfilename);

      //clean up (just in case)
      System.Runtime.InteropServices.Marshal.ReleaseComObject(objJRO);
      objJRO = null;
    }

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

David Smith16y ago
I copied and pasted the example wrong. I fix the problem compacting access database

for those who want to know how to compact access database.

Code Snippet below:

   CompactAccessDB(Application.StartupPath +"Database.mdb");

public static void CompactAccessDB(string mdbPathFilename)
    {
      object[] oParams;

      //create an inctance of a Jet Replication Object
      object objJRO = Activator.CreateInstance(Type.GetTypeFromProgID("JRO.JetEngine"));

      //filling Parameters array
      oParams = new object[] {
                                "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "+ mdbPathFilename +"",   //Explicit location where of the source mdb you are going to compact
                                "Provider=Microsoft.Jet.OLEDB.4.0;Data" +
                                " Source= "+Application.StartupPath +"\\TempDatabaseLocation\\Database.mdb;Jet OLEDB:Engine Type=5" //Location we are going to copy the database to
                             };

      //invoke a CompactDatabase method of a JRO object
      //pass Parameters array
      objJRO.GetType().InvokeMember("CompactDatabase",
                                    System.Reflection.BindingFlags.InvokeMethod,
                                    null,
                                    objJRO,
                                    oParams);

      //Database is compacted now to a new location "Application.StartupPath + "\\TempDatabaseLocation\\Database.mdb"
      //let's copy it over an old one and delete it
      System.IO.File.Delete(mdbPathFilename);
      System.IO.File.Move(Application.StartupPath + "\\TempDatabaseLocation\\ElauDatabase.mdb", mdbPathFilename); replacing the database you compacted back to its original location

      //clean up (just in case)
      System.Runtime.InteropServices.Marshal.ReleaseComObject(objJRO);
      objJRO = null;
    }
Sam Hobbs16y ago
I think it will help if you clarify what you are asking for help with. I don't undestand what the question is.

Also, your code does not compile for me. If I copy the following line and paste it into a program:

string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Database.mdb";
Then I get the error:

error CS1009: Unrecognized escape sequence