Basic question on sharing Access DB between Access and an application

[Grant Schenck]

Hello,

I wrote a .NET application which uses an Access Database. It only does
reads from the DB, never writes to it.

I intended for the user to be able to modify the data by just running MS
Access and directly opening the table(s) and modifying the data.

My application uses a file watcher to know when the table has changed and in
response, re-queries the rows in one of the tables.

However, some of the time (but not all) my app gets an exception ("Could not
lock file") when it goes to read the table in response to seeing that the
Access MDB file changed:

System.Data.OleDb.OleDbException: Could not lock file.
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString
constr, OleDbConnection connection)
at
System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection
owningConnection, DbConnectionPoolGroup poolGroup)
at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at STPSCallRouter.ServiceCallRouter.ReadAndProcessRoutePointsFromDB()

So, my question is basic. Is there a way to allow this to work?

I initially had this as my connection string:
"Provider=" + g_strDBProvider + @"; Data Source=" + m_Settings.DBPath +
"; Mode=Read"

But then tried changing the mode to "Share Deny None" but that didn't stop
the exceptions from happening some of the time.

 

[Stefan Hoffmann]

hi Grant,

However, some of the time (but not all) my app gets an exception ("Could not
lock file") when it goes to read the table in response to seeing that the
Access MDB file changed:

Have you set your .mdb to auto repair and compact on close?


mfG
--> stefan <--

 

[Grant Schenck]

No I haven't. I see an option in the General tab "Compact on Close". I
assume that is one to set. Where do I find the auto repair?

Also, why would this address the issue of an application getting a "Could
not lock file" exception?

Thanks,

 

[Stefan Hoffmann]

No I haven't. I see an option in the General tab "Compact on Close". I
assume that is one to set. Where do I find the auto repair?

Yup, that's what I meant.

Also, why would this address the issue of an application getting a "Could
not lock file" exception?

A possible race condition.



mfG
--> stefan <--

 

[Howard Burgman]

Hello,

I wrote a .NET application which uses an Access Database. It only does
reads from the DB, never writes to it.

I intended for the user to be able to modify the data by just running MS
Access and directly opening the table(s) and modifying the data.

My application uses a file watcher to know when the table has changed and
in response, re-queries the rows in one of the tables.

However, some of the time (but not all) my app gets an exception ("Could
not lock file") when it goes to read the table in response to seeing that
the Access MDB file changed:

System.Data.OleDb.OleDbException: Could not lock file.
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString
constr, OleDbConnection connection)
at
System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection
owningConnection, DbConnectionPoolGroup poolGroup)
at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at STPSCallRouter.ServiceCallRouter.ReadAndProcessRoutePointsFromDB()

So, my question is basic. Is there a way to allow this to work?

I initially had this as my connection string:
"Provider=" + g_strDBProvider + @"; Data Source=" + m_Settings.DBPath +
"; Mode=Read"

But then tried changing the mode to "Share Deny None" but that didn't stop
the exceptions from happening some of the time.

 

[Grant Schenck]

No I haven't. I see an option in the General tab "Compact on Close". I
assume that is one to set. Where do I find the auto repair?

Also, why would this address the issue of an application getting a "Could
not lock file" exception?

 

[John W. Vinson]

No I haven't. I see an option in the General tab "Compact on Close". I
assume that is one to set. Where do I find the auto repair?

Compact and repair are two parts of the same operation. However, you should
leave "Compact on Close" *OFF*, not turn it on. There have been many reports
of problems with it.

Also, why would this address the issue of an application getting a "Could
not lock file" exception?

Absolutely. Compacting requires that the file be locked, and if someone else
has it open or allocated, it will be unable to do so. If you turn off Compact
on Close you should avoid this message (at least for this cause).