Is there a way to open an Access db as shared with ES?

I've not worked with Access much so I am not familiar with it but unfortunately I am stuck with it.

With ES I was easily able to create a DotNetNuke module and a Web Service to supply data for a page. My problem is that if the db is in use by others (it is used by a number of users) and my ws tries to access a table it can't. I guess there must be something simple I can do in the ws to share the db.

Declan

11 months later and I am only getting around to this one

I have 2 MS Access dbs in current use. One front end UI and the second the backend. I can open several instances of the db using the front end. However when I use ES classes to open the db I get an error stating thet the file is in use. This suggests to me that using ES is trying to lock the db exclusively. I have googled and not come up with anything that works.

Can anyone offer a suggestion as to how I can open the database with my web service using Entity Spaces? Unfortunately my knowledge of MS Access is almost nil

Declan

        Dim strConn As String = String.Empty
        Dim strSQL As String = String.Empty
        Dim da As OleDbDataAdapter
        Dim ds As New DataSet

        Try
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\office2002\test.mdb"
            strSQL = "SELECT * FROM clubs"

            da = New OleDbDataAdapter(strSQL, strConn)
            da.Fill(ds)

so what is wrong with this:

	<EntitySpaces>
		<connectionInfo default="MSAccess">
			<connections>
				<add name="MSAccess" providerMetadataKey="esDefault" 
             					sqlAccessType="DynamicSQL" 
					provider="EntitySpaces.MSAccessProvider" 
					providerClass="DataProvider"
				             connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Office2002\test.mdb;"/>
			</connections>
		</connectionInfo>
	</EntitySpaces>

Error:  Server was unable to process request. ---> Could not use ''; file already in use.

However if MS Access does not have the file open the above Entity Spaces connection works.

Declan

This post discusses just the opposite of what you want, but shows how to add a "Mode" option in the connection string.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=415446&SiteID=1

Here is an updated link that enumerates the modes available, maybe try 16?

http://msdn2.microsoft.com/en-us/library/ms806213.aspx

The error message looks odd

Error:  Server was unable to process request. ---> Could not use ''; file already in use.

Shouldn't there be a filename between the single quotes?

I am able to open multiple apps hitting Access with the standard connection string as you have shown. But, as I understand it, you can do the same on the client side. The issue is on the server side.

Also, there appears to be issues attaching to a server side Access database after upgrading to IE7:

http://www.thescripts.com/forum/thread571999.html

This is going to be very difficult to debug remotely, as EntitySpaces takes the connection string and our Access provider essentially constructs standard ADO.NET using OleDB as shown in your code.

OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = cmd;

esTransactionScope.Enlist(da.SelectCommand, 
    request.ConnectionString, CreateIDbConnectionDelegate);
da.Fill(dataTable);
esTransactionScope.DeEnlist(da.SelectCommand);

Declan, try running down some of these links, seems this is a common issue with Access.

http://www.google.com/search?hl=en&q=%22Could+not+use+%3B+file+already+in+use.%22+Access

Boy do I feel like a plank...

After trawling through all the links and trying all types of Mode someone on one of the sites mentioned sysinternals. Then the light came on! I fired up FileMon and hit the jackpot!

All I had to do was add NEWORK SERVICE with appropriate rights to the folder on the server. Doh!!! Of course the web service needs NETWORK SERVICE on W2K3 Server. That is why my stand alone app using OleDB worked and my Web Service, not Entity Spaces I might add, failed.

Thanks for bearing with me on this. 

Problem solved - and one very happy camper here

Declan