How to Connect to Access mdb "Share Deny None" in code

[Felecha]

I built a Windows Service in VB.Net, that works with an Access 2003 database behind the scenes, no user interaction, it monitors the actions of the main computer in our system. We just rebuilt it - making a second Service that handles some logic for sending messages to the maintenance folks if there is a problem. It also has to query the database. Up to now it's been fine, but now that there are 2 apps we have seen some kind of conflict or contention for opening connections. None of us is a database expert, we just selected Access as a reasonable answer to our need for a database that would work and wouldn't need too much investment or learning curve.

 

The code uses System.Data.OleDb for OleDbConnection, OleDbCommand, and OleDbDataReader classes.

 

I built a little test app that starts a thread that continuously opens, queries, reads, and closes. When I get enough copies of that app running I see the same failures, with Exception.Message text saying one of 3 things:

 

1. Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt.

2. You attempted to open a database that is already opened exclusively by user 'Admin' on machine 'DEVELOPER'. Try again when the database is available.

3. Could not use ''; file already in use.

 

Someone told me on a forum to make sure that the Access properties were set to default to Open Shared. I discovered that when I open the database in Access while a bunch of my little test apps are running and failing, the failures stop. I believe that there is something in the connection from Access that is telling the database to be available to other connections but when opened from my code it's not doing that. I figure that I have to give my OleDbConnection.ConnectionString the right set of properties to make it work in code

 

I've found several sites that indicate Connection String properties for Jet OLEDB but can't find one that works. It looks like "Share Deny None" should be the one I want but I can't find the right syntax. I'm quite surprised that when I create one from the .Net toolbox and use the GUI to set the settings for it, "Mode=Share Deny None" is what Windows generates for me, but it doesn't work. When I use that in the string, I get "Could not lock file."

 

Anyone know the .Net code for telling an Access database to open as Shared for all users?

[Felecha]

Here's the current ConnectionString

 

"Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Shared\Data\Hands.mdb;" & _

"Mode=Share Deny None;" & _

"User ID=Admin"

 

When i open the database in Access I can see the .ldb file appearing in Windows Explorer and deleting when I close it. But running my test apps with the above ConnectionString, with Mode=Share Deny None, which is exactly what .Net writes for me if I let the ToolBox put an OleDbConnection in there for me, does not start an ldb file. I have been reading about ldb files and it's clear that they get created whenever the database is accessed in Shared mode. If it's opened in Exclusive mode the ldb is not created. So how can the above ConnectionString open the darn thing in Exclusive mode????

 

Hoping, and thankful for any help

[Felecha]

Well, here's one more clue, and it still looks like something to do with Shared and Exclusive.

 

In my test app I find that if I include "Mode=Share Deny None" in the ConnectionString, ALL FAILURES say "Could not lock file." If I remove that entirely from the ConnectionString, so that presumably Access has to use its default (which I understand is Shared), the Exception messages go back to the 3 I mentioned in the first post.

 

And, I'm curious - why does it say Could not use '' and Cannot open database '' ? All the references I see to this error message on the net have some kind of database name in the quotes, Cannot open database '<C;\Data\TestDatabase.mdb'

[fyz]

Hi,

 

I recently used my code to connect the database but get cannot open error. Does it mean my mdb file is corrupted? I once used a tool called Advanced Access Repair to repair my file. Althoug it worked rather well, but it is not free. Is there other ways to repair it?

 

 

Alan