Disable/Flush OleDbConnection Cache

Question

I've been fighting with OleDbConnection for a while now trying to get it to not cache. Basically I am accessing a shared Access database, which is being written to from another application, and then I'm reading back values (having checked that it is flushed via the Last Write time and a subsequent 1 second delay).

Unfortunately, this is entirely unreliable.

I've been reading (and going insane) how to disable the connection pooling, and am subsequently, after each possible update, performing the following before reconnecting:

_connection.Close();
_connection.Dispose();
_connection = null;
OleDbConnection.ReleaseObjectPool();
GC.Collect();

In addition to this, the connection string disables connection pooling with OLE DB Services = -2. Finally, I have also changed PageTimeout to '10' in the registry for Jet 4.0.

All of these measures are unfortunately having no effect. Now the only thing I can think of doing is what is mentioned in this Microsoft KB Article, and call JRO.JetEngine.RefreshCache. The only issue with that is that it's argument is an ADODB.Connection. I'd rather not rewrite my whole database layer and where the records are being read by my software to use a legacy COM object just to have this functionality, but it appears that it may well be the only way.

My question is, whilst currently undergoing this task of rewriting to use ADODB (not even ADO.NET!), is it possible to disable the caching of an OleDbConnection?

Answer 1

Finally, I found a workaround: Use OdbcConnection instead of OleDbConnection.

This is the old code:

string mdbConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + mdbFile + ";OLE DB Services=-2";
using (OleDbConnection conn = new OleDbConnection(mdbConnectionString)) {
    conn.Open();
    //Do your query
}

And this is new one:

string mdbConnectionString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=" + mdbFile;
using (OdbcConnection conn = new OdbcConnection(mdbConnectionString)) {
    conn.Open();
    //Do your query
}

All things work fine.