Corrupting Access databases when inserting values

Question

Recently, a program that creates an Access db (a requirement of our downstream partner), adds a table with all memo columns, and then inserts a bunch of records stopped working. Oddly, there were no changes in the environment that I could see and nothing in any diffs that could have affected it. Furthermore, this repros on any machine I've tried, whether it has Office or not and if it has Office, whether it's 32- or 64-bit.

The problem is that when you open the db after the program runs, the destination table is empty and instead there's a MSysCompactError table with a bunch of rows.

Here's the distilled code:

var connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=corrupt.mdb;Jet OLEDB:Engine Type=5";

// create the db and make a table
var cat = new ADOX.Catalog();
try
{
    cat.Create(connectionString);

    var tbl = new ADOX.Table();
    try
    {
        tbl.Name = "tbl";
        tbl.Columns.Append("a", ADOX.DataTypeEnum.adLongVarWChar);
        cat.Tables.Append(tbl);
    }
    finally
    {
        Marshal.ReleaseComObject(tbl);
    }
}
finally
{
    cat.ActiveConnection.Close();
    Marshal.ReleaseComObject(cat);
}

using (var connection = new OleDbConnection(connectionString))
{
    connection.Open();

    // insert a value
    using (var cmd = new OleDbCommand("INSERT INTO [tbl] VALUES ( 'x' )", connection))
        cmd.ExecuteNonQuery();
}

Here are a couple of workarounds I've stumbled into:

1. If you insert a breakpoint between creating the table and inserting the value (line 28 above), and you open the mdb with Access and close it again, then when the app continues it will not corrupt the database.
2. Changing the engine type from 5 to 4 (line 1) will create an uncorrupted mdb. You end up with an obsolete mdb version but the table has values and there's no MSysCompactError. Note that I've tried creating a database this way and then upgrading it to 5 programmatically at the end with no luck. I end up with a corrupt db in the newest version.
3. If you change from memo to text fields by changing the adLongVarWChar on line 13 to adVarWChar, then the database isn't corrupt. You end up with text fields in the db instead of memo, though.

A final note: in my travels, I've seen that MSysCompactError is related to compacting the database, but I'm not doing anything explicit to make the db compact.

Any ideas?

Answer 1

As I replied to HasUp: According MS support, creation of Jet databases programmatically is deprecated. I ended up checking in an empty model database and then copying it whenever I needed a new one. See http://support.microsoft.com/kb/318559 for more info.