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/micro...80ef822df6e91c

[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

"JonnyD" <ma*********@gm ail.com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .

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?

[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" <ma*********@gm ail.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

I found this thread but I didn't follow how the problem was fixed.
http://groups.google.com/group/micro...80ef822df6e91c

[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 "autogenera te" 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" <greg_burns@DON T_SPAM_ME_hotma il.com> wrote in message
news:OB******** ******@TK2MSFTN GP15.phx.gbl...

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" <ma*********@gm ail.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

I found this thread but I didn't follow how the problem was fixed.
http://groups.google.com/group/micro...80ef822df6e91c

[Greg Burns]

Here is the KB I mentioned:

http://support.microsoft.com/default...b;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.

"Greg Burns" <greg_burns@DON T_SPAM_ME_hotma il.com> wrote in message
news:%2******** ********@TK2MSF TNGP15.phx.gbl. ..

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 "autogenera te" 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" <greg_burns@DON T_SPAM_ME_hotma il.com> wrote in message
news:OB******** ******@TK2MSFTN GP15.phx.gbl...

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" <ma*********@gm ail.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

I found this thread but I didn't follow how the problem was fixed.
http://groups.google.com/group/micro...80ef822df6e91c

[Mark Rae]

"John Timney ( MVP )" <ti*****@despam med.com> wrote in message
news:uE******** ******@tk2msftn gp13.phx.gbl...

Well your problem is that Access is a single user database
That simply isn't true...
http://office.microsoft.com/en-gb/as...408601033.aspx
and sucks somewhat for this type of scneario.

That, however, is perfectly true.

[Mark Rae]

"JonnyD" <ma*********@gm ail.com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .

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.


Juan T. Llibre, ASP.NET MVP
ASP.NET FAQ : http://asp.net.do/faq/
Foros de ASP.NET en Español : http://asp.net.do/foros/
=============== =============== ========
"Mark Rae" <ma**@mark-N-O-S-P-A-M-rae.co.uk> wrote in message
news:OX******** ******@TK2MSFTN GP09.phx.gbl... "JonnyD" <ma*********@gm ail.com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .

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...