I have been looking around for a while and the only way I have found to compact and repair an MS Access database through ASP.NET is by using either DAO or ADO, which are pre-dot NET.

I have noticed in my OLE DB connection strings created by Visual Studio .NET there are properties like:
Jet OLEDB:Compact Without Replica Repair=false;
Jet OLEDB:Don't Copy Locale on Compact=false;

Does this mean that .NET takes care of compact & repair operations for you, or is there a way to use ADO.NET to do this?

Basically I am asking because I am developing a forum system and have to use MS Access as the backend, and even though the usage is fairly light all my tests show the database will need regular compact and repair operations to keep in good working order.

Cheers guys,
Adam

Solved

Ok I pretty much have it cracked, heres what to do;

1) Open your project in Visual Studio .NET
2) Right click References in the Solution Explorer and select 'Add Reference...'
3) Click the COM tab and select 'Microsoft Jet and Replication Objects 2.x Library' 'x' will be the version you have.
4) Click the Select button and then Ok.

Now you should have references to ADODB and JRO added to your project.

To use them in your code do the following (this is in c#).

-------------------------------------------------------------------------------
//make sure you import the needed namespaces (System.IO is needed to copy/delete files at end!)
using System;
using System.IO;
using System.Configuration;

//now to the code

JRO.JetEngineClass jro;
string newConStr;
string conStr;

string path = <path to your database directory>;
string originalDb = <name including extension of the original Database>;
string newDb = <name including extension of the new Database to create>;

//instantiate the Jet Replication Object
jro = new JRO.JetEngineClass();

//get our connection string for the original database (mine is stored in my web.config file)
conStr = ConfigurationSettings.AppSettings["ConnectionString"];
//create a connection string for the compacted and repaired database we are going to create
newConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;Data Source=" + path + newDb;

//now lets compact the original database to our new one
jro.CompactDatabase(conStr, newConStr);

//ok, now lets overwrite the original Database file with the compacted one
File.Copy(path + newDb, path + originalDb, true);
//finally lets delete the database we made earlier since it is no longer needed
File.Delete(path + newDb);

-------------------------------------------------------------------------------------------

Hope this helps anyone else who is looking to compact and repair their Access Databases

This was so helpfull in my project....

 

Thanks

 

Thanks Adam It really helped me a lot.My DB whose All tables when delete were showing 230 MB now it is working Fine.

Thanks again