How to do Connect.Open() in Shared Mode using VB.Net

[K1BBQ]

It looks like the VB.Net code (System.Data.OleDb) that I've used in my Windows Service for a long time has been opening the Access 2003 database for queries in Exclusive Mode. I did not set that explicitly in my ConnectionString, I didn't even know it would matter. I'm not a database programmer, I just learned what I needed in order to write my application to use a database.

All I have had in the ConnectionString is "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Data\Data.mdb" and that has worked fine.

When we added a second Service to our product, which also queries the same database, we found a few instances of Exceptions with messages 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. Could not use ''; file already in use.
3. You attempted to open a database that is already opened exclusively by user 'Admin' on machine 'DEVELOPER'. Try again when the database is available.

I built a test app that would start a Thread that would continuously open, query, read, and close. Then I can run 10 of them at once and read the Exception messages that are written to a text file.

I studied on internet sites and thought I had the answer- I would explicitly add "Mode=Share Deny None" to the ConnectionString. But adding that results instead in errors that say "Could not lock file."

When I run it WITH the Mode property explicitly set, no .ldb file ever gets created. If I run it without the Mode set in code, the ldb files get created and deleted but still there are the first error message types.

And why does it tell me it can't open database ''? When I see references to that error on websites it has the database name in there.

Any ideas?

Thanks so much

 

[jjlogan]

Any ideas?
I was having the problem - Could not use ''; file already in use. - in Access 2003 when I tried to open the ADO connection "CurrentProject.Connection" within the database I already had opened from the File menu. My VBA code was in a Module subroutine that does update of records. What I found out was that I had opened the database file for Exclusive use because I did not want the other users in there while I did this mass update of records on a particular field. However, having the file opened for exclusive use then locked my VBA code entry, it appears. I had no more problem after closing Access, then reopening using the Open button and Option "Open" instead of "Open for Exclusive ...". Hope this helps or confirms anything.
Jeff Logan