Help connecting to Access DB from web service

[Jeff Richardson]

I am creating a web service that returns data from an Access 2000 database
using VS2005. The Access database is located on a different server than
where the web service is running. The Access database is also opened by a
different desktop application running on various workstations on the
network.

While developing / debugging the web service is hosted on my development
machine using the 'ASP.NET Development Server' that is included with VS2005.
When deployed it is being host with IIS.

I am using the following connection string:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source="\\slo-science1\data\PBI Chem
data\PBIChemDatabase.mdb"

If the database has NOT opened by any of the workstations running the
desktop application then the web service runs correctly on both my
development machine as well as on the IIS server.

If the database has been opened by any of the workstations running the
desktop application the web service runs correctly ONLY on my development
machine and fails on IIS with the following error:

System.Data.OleDb.OleDbException: Could not use ''; file already in use.

What are the differences between the 'ASP.NET Development Server' and IIS
that would effect how a web service opens a remote Access database?

Does anyone know how what settings can be changed so that the web service
runs correctly on IIS?

Thanks in advance,
Jeff Richardson

 

[Mary Chipman [MSFT]]

Is there a reason why you can't use SQL Express? Jet is totally
unsuited as the data store for an ASP.NET application. If you persist,
you are always going to have headaches. One of the reasons is locking,
as you have discovered. It's not hard to Import Jet data to Express,
and Express is free, so there is no compelling reason not to use it.

--Mary

 

[Jeff Richardson]

Hi Mary,

Thank you for your reply.

The compelling reason is that the desktop application, mentioned below, is a
commercial application that uses the Access db as its native file format. I
can not abandon the commercial application because it provides functionality
that I have not been able to duplicate myself.

Are you aware of a work-around form my file locking problems while running
under IIS?

Thanks in advance,
Jeff.

 

[Paul Clement]

¤ I am creating a web service that returns data from an Access 2000 database
¤ using VS2005. The Access database is located on a different server than
¤ where the web service is running. The Access database is also opened by a
¤ different desktop application running on various workstations on the
¤ network.
¤
¤ While developing / debugging the web service is hosted on my development
¤ machine using the 'ASP.NET Development Server' that is included with VS2005.
¤ When deployed it is being host with IIS.
¤
¤ I am using the following connection string:
¤
¤ Provider=Microsoft.Jet.OLEDB.4.0;Data Source="\\slo-science1\data\PBI Chem
¤ data\PBIChemDatabase.mdb"
¤
¤ If the database has NOT opened by any of the workstations running the
¤ desktop application then the web service runs correctly on both my
¤ development machine as well as on the IIS server.
¤
¤ If the database has been opened by any of the workstations running the
¤ desktop application the web service runs correctly ONLY on my development
¤ machine and fails on IIS with the following error:
¤
¤ System.Data.OleDb.OleDbException: Could not use ''; file already in use.
¤
¤ What are the differences between the 'ASP.NET Development Server' and IIS
¤ that would effect how a web service opens a remote Access database?
¤
¤ Does anyone know how what settings can be changed so that the web service
¤ runs correctly on IIS?

What the error is telling you is that there is a permissions problem with the remote resource. In
order to work with an Access database each user (or the ASPNET or NetworkService user if the app is
not configured for impersonation) must have full permissions to the resource where the database is
located. This is required to create, update and delete (after the database has been closed) the
corresponding .LDB file.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Mary Chipman [MSFT]]

Gotcha -- Jet can be a lot more flexible as long as you don't care
about security and work around its inherent fragility. It looks like
Paul has given you the answer to your question. Just out of curiosity,
what is the functionality that you can't duplicate?

--Mary