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

F

Felecha

Active member
Joined
Jun 14, 2004
Messages
34
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 its 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 wouldnt 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 its not doing that. I figure that I have to give my OleDbConnection.ConnectionString the right set of properties to make it work in code

Ive found several sites that indicate Connection String properties for Jet OLEDB but cant find one that works. It looks like "Share Deny None" should be the one I want but I cant find the right syntax. Im 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 doesnt 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?
 
Heres 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 its clear that they get created whenever the database is accessed in Shared mode. If its 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
 
Well, heres 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, Im 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
 
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
 
Back
Top