MS Access DB used by both a local App and .net Web App

[JonnyD]

I am working on a project to build a reporting web app from an exsiting
database that is controled by a local application. The application that
has control over the database creates a lock file to the database and
at that point I can no longer access the database with my web app. Is
there anyway around this? All I need is read only access to the
database to get some report information to display on the web page.

The error I get on the web app when I try to access the locked db is
"Could not use ''; file already in use."

Anyone have experiance with this?

 

[JonnyD]

I found this thread but I didn't follow how the problem was fixed.
http://groups.google.com/group/micr...q=ASP.NET+&+MS+Access&rnum=1#f380ef822df6e91c

 

[John Timney \( MVP \)]

Well your problem is that Access is a single user database and sucks
somewhat for this type of scneario.

If all your doing is reporting (a read activity), copy the file when you
access it using asp.net to a temp file and read from that instead. That way
you should always have access and will get up to date data.

--
Regards

John Timney
ASP.NET MVP
Microsoft Regional Director

 

[Greg Burns]

I think my suggestion from 3 years ago still is good advice:

"Downloaded FileMon and took another look. Appears ASPNET didn't have the
rights I thought it did to the database and the folder it resides in. Fixed
that, and the problem cleared up"

The ASPNET user must have read/write access to the folder containing your
..mdb file.

Greg

 

[JonnyD]

I would like the data to be as real time as possable though, would it
be slow if i were to copy the db for every page refresh, or even
everytime a querry is run?

 

[Greg Burns]

MS Access is weird. We run into this all the time on our network where we
want some users to have full rights to a database, and others have only read
rights. Turns out if the read-only people connect while a full-rights
person is connected they get "blocked"(!) See, no matter what your rights
to the .mdb, you have to have full-rights to the .ldb (maybe even create it,
if it is not present) in order for Access to work properly. (Access suxs
for this, but wacha goin do).

The solution for multiple users on a network share is to place the .mdb file
in a folder. Permission the folder for with full-rights for everbody. Then
restricts rights to the .mdb file itself for the read-only folks. This
allows everybody to create/write the .ldb file. A nasty side effect is that
the read-only folks can create/write to any other files in the subfolder.
You cannot just permission the .ldb explicitly, because sometimes it get
deleted.

(There is a KB article on the above, I'll try and track it down._

OK. How does this apply to a web app...

If the .mdb was on the same server, you would just give the local ASPNET
user full rights to the folder that contains the .mdb. There, problem
solved.

Since you database is on a different server you have to do more. This is
what I do all time; "Pass-through authentication".

The steps:
#1. Change the password for your ASPNET user on the webserver. (If will give
dire warnings, but just do it). Set it to something secret.
#2. Open machine.config on webserver. Fine the processModel section.
Change password from "autogenerate" to your new password.

Run iisreset to restart webserver (or just reboot webser). (If you ever
apply .dotnet SP1 or a future SP; it will reset the ASPNET password back to
some unknown, and things will break - cause it won't match what the
machine.config says. Just reset it again, and double check the account
didn't get lock out.)

Now ASP.NET is running on your webserver with the still limited user
account; ASPNET, but now with a known password. (that is the key to making
this work)

#3. On network share server, create a local account named "ASPNET". Make
password the same as above.
#4. Permission the folder containing your .mdb file on this server, so that
this local account has read-write access to the entire folder(!).

You could have made a domain account, and changed your webserver to run
using it instead. But then you would have to assign that domain account the
same rights as the local ASPNET user on the webserver for things to work
correctly. (That is tricky to do, and not necessary using pass-through).
There are KB on how to do this, but doesn't seem worth the hassel to me.

Added bonus: this method works also if you ever need to connect to SQL
Server running on another server than your webserver.

Let me know what you need cleared up. I'm typing pretty fast.

Greg

 

[Greg Burns]

Here is the KB I mentioned:

http://support.microsoft.com/default.aspx?scid=kb;en-us;136128
Required Folder Permissions
If you plan to share a database, the .mdb file should be located in a folder
where users have read, write, create, and delete (or full control)
permissions. Even if you want users to have different file permissions (for
example, some read-only and some read-write), all users sharing a database
must have read, write, and create permissions to the folder. You can,
however, assign read-only permissions to the .mdb file for individual users
while still allowing full permissions to the folder.

 

[Mark Rae]

Well your problem is that Access is a single user database

That simply isn't true...
http://office.microsoft.com/en-gb/assistance/HP052408601033.aspx

and sucks somewhat for this type of scneario.

That, however, is perfectly true.

 

[Mark Rae]

Anyone have experiance with this?

Yes, and you're not going to like the answer.

Put simply, an Access database is *totally* unsuited to what you're trying
to do with it, for several reasons:

1) Although the Jet engine is inherently multi-user, it is a desktop
database engine, not a server database engine. This means that all queries
run on the client, not the server.

2) Whenever an Access database is opened, a corresponding locking file (e.g.
Northwind.ldb) is created with the same name and in the same folder as the
database. This means that every user of the database needs to have,
essentially, full control permissions on the network folder.

3) Access databases restrict the number of concurrent connections from IIS -
I can't remember exactly what that figure is, but it's quite low.

4) If you have the Access application installed on your desktop and,
inadvertently or even intentionally, open a database as "Exclusive" (e.g. to
do some maintenance or whatever), Access will remember that setting and will
always subsequently open that database as Exclusive until you explicitly
tell it not to, thereby preventing any other user from connecting to it.


Do yourself a huge favour and use SQL Server / MSDE / mySql...

 

[Juan T. Llibre]

A caveat and some info...

re:

This means that every user of the database needs to have, essentially, full control
permissions on the network folder.

In a anonymous-access web application there's only one user.

re:

3) Access databases restrict the number of concurrent connections from IIS - I can't
remember exactly what that figure is, but it's quite low.

I've run web applications with a sustained 60+
concurrent connections without a problem.

Fine-tuning your connections ( releasing them
as soon as you're done with them ) helps a lot.

re:

Do yourself a huge favour and use SQL Server / MSDE / mySql...

MSDE will outperform Access on the web, and mySql
will vastly outperform Access on the web, and SQL Server will
outperform all of them.

Something to keep in mind is that SQL Server Express is already
available at MSDN Subscriber downloads and will be released
to the general public in less than 10 days.

It will easily outperform MSDE and give mySql a run for its money
....and it's free.

For a desktop application with less than 60 concurrent users,
however, Access is a fine solution which offers many advantages
and built-in resources.

 

[John Timney \( MVP \)]

I guess it depends on how big your database is? Its only one option.....and
may be entirely unsuitable for your needs.

--
Regards

John Timney
ASP.NET MVP
Microsoft Regional Director

 

[Mark Rae]

In a anonymous-access web application there's only one user.

Yes, but the OP is talking about sharing the same Access database between
internal users over a network and external users over the web.

I've run web applications with a sustained 60+
concurrent connections without a problem.

OK - I was under the impression that there was some intentional internal
limit to the number of concurrent connections.

Fine-tuning your connections ( releasing them
as soon as you're done with them ) helps a lot.

Yes indeed - good advice, and not just for Access...

 

[Juan T. Llibre]

re:

Yes, but the OP is talking about sharing the same Access database between internal users
over a network and external users over the web.

OK, I didn't see that.

re:

OK - I was under the impression that there was some intentional internal limit to the
number of concurrent connections.

MSDE does that, but not Access.

But, there isn't really a "limit" to the number of connections in MSDE.

What happens is that, after 8 concurrent connections have been reached,
MSDE will throttle down the new connections, although none are denied.

They will all be served, with a slight delay for all connections above 8.

 

[John Timney \( MVP \)]

You are of course correct - in that it "can" be shared. This was my chain of
thought:

Unless you open it in shared mode at connection time its pretty much a
single user database, as once its opened by anyone in exclusive mode its
locked for access to everyone else until that lock is cleared. .Also, if
you have any access client opening any tables for any form of schema
editing, you will by default lock it to that user alone and render the
database exclusively locked until that user releases the system - forcing
single use. This is a problem with shared Access databases, as you cant
compact or repair a shared DB file if the LDB claims its shared and you
could end up with a corrupted DB due to sharing problems and invalid record
locking. All round, Access is not the ideal choice for a shared
database.....

--
Regards

John Timney
ASP.NET MVP
Microsoft Regional Director

 

[Greg Burns]

re:

MSDE does that, but not Access.

http://support.microsoft.com/default.aspx?scid=kb;en-us;136128#XSLTH3131121123120121120120

Note Although a file-server solution can support up to 255 simultaneous
users, if the users of your solution will be frequently adding data and
updating data, it is a good idea for an Access file-server solution to
support no more than 25 to 50 users.

 

[Juan T. Llibre]

Hi, Greg.

Don't forget that in an anonymous web environment there's only *one* user.

The caveats in :

"For each person who opens a shared database, the Jet database engine
writes an entry in the database's .ldb file. The size of each .ldb entry is 64 bytes.
The first 32 bytes contains the computer name (such as JohnDoe).
The second 32 bytes contains the security name (such as Admin)."

don't apply, since only one user is accessing the Access db.

But, the limits to Access db's are quite evident.
For web usage, MSDE, and now SSE, are far better choices.

 

[Paul Clement]

¤ I am working on a project to build a reporting web app from an exsiting
¤ database that is controled by a local application. The application that
¤ has control over the database creates a lock file to the database and
¤ at that point I can no longer access the database with my web app. Is
¤ there anyway around this? All I need is read only access to the
¤ database to get some report information to display on the web page.
¤
¤ The error I get on the web app when I try to access the locked db is
¤ "Could not use ''; file already in use."
¤
¤ Anyone have experiance with this?

You're probably on the right track. The .LDB file is usually the stinker in this scenario and
typically requires that any user accessing the database have *full* access to the folder where the
database is located. That would be first thing I would check.


Paul
~~~~
Microsoft MVP (Visual Basic)