How do I create a Microsft Jet (Access) database without an interop assembly?

Question

I need to create an access (mdb) database without using the ADOX interop assembly.

How can this be done?

Answer 1

Before I throw away this code, it might as well live on stackoverflow

Something along these lines seems to do the trick:

if (!File.Exists(DB_FILENAME))
{
    var cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DB_FILENAME;

    // Use a late bound COM object to create a new catalog. This is so we avoid an interop assembly. 
    var catType = Type.GetTypeFromProgID("ADOX.Catalog");
    object o = Activator.CreateInstance(catType);
    catType.InvokeMember("Create", BindingFlags.InvokeMethod, null, o, new object[] {cnnStr});

    OleDbConnection cnn = new OleDbConnection(cnnStr);
    cnn.Open();
    var cmd = cnn.CreateCommand();
    cmd.CommandText = "CREATE TABLE VideoPosition (filename TEXT , pos LONG)";
    cmd.ExecuteNonQuery();

}

This code illustrates that you can access the database using OleDbConnection once its created with the ADOX.Catalog COM component.

Answer 2

You don't need Jet(major headache) installed, if you use this connection string in .net 3.5

Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=C:\myFolder\myAccess2007file.accdb;Persist
Security Info=False;

This should work on access 2007 and below

Answer 3

Interesting question -- I've never thought to create one on the fly like this. I've always included my baseline database as a resource in the project and made a copy when I needed a new one.

Answer 4

I've done the same as Autsin, create an Access db then included it into my project as a managed resource. Once there, it is included in the compiled code and you can copy it to hard disk as many times as you want. Empty databases are relatively small too, so there isn't much overhead.

The added bonus is the ability to set up the database if you know how it will be used or what tables will be added every time, you can reduce the amount of coding and slow database queries.