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

Discussion in 'ASP .Net' started by JonnyD, Oct 28, 2005.

  1. JonnyD

    JonnyD Guest

    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, Oct 28, 2005
    #1
    1. Advertising

  2. JonnyD

    JonnyD Guest

    JonnyD, Oct 28, 2005
    #2
    1. Advertising

  3. 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" <> wrote in message
    news:...
    >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?
    >
     
    John Timney \( MVP \), Oct 28, 2005
    #3
  4. JonnyD

    Greg Burns Guest

    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" <> wrote in message
    news:...
    >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
    >
     
    Greg Burns, Oct 28, 2005
    #4
  5. JonnyD

    JonnyD Guest

    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?
     
    JonnyD, Oct 28, 2005
    #5
  6. JonnyD

    Greg Burns Guest

    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" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
    news:...
    >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" <> wrote in message
    > news:...
    >>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
    >>

    >
    >
     
    Greg Burns, Oct 29, 2005
    #6
  7. JonnyD

    Greg Burns Guest

    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.

    "Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
    news:%...
    > 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" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
    > news:...
    >>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" <> wrote in message
    >> news:...
    >>>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
    >>>

    >>
    >>

    >
    >
     
    Greg Burns, Oct 29, 2005
    #7
  8. JonnyD

    Mark Rae Guest

    Mark Rae, Oct 29, 2005
    #8
  9. JonnyD

    Mark Rae Guest

    "JonnyD" <> wrote in message
    news:...

    > 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...
     
    Mark Rae, Oct 29, 2005
    #9
  10. 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" <> wrote in message
    news:...
    > "JonnyD" <> wrote in message
    > news:...
    >
    >> 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, Oct 29, 2005
    #10
  11. 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

    "JonnyD" <> wrote in message
    news:...
    >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?
    >
     
    John Timney \( MVP \), Oct 29, 2005
    #11
  12. JonnyD

    Mark Rae Guest

    "Juan T. Llibre" <> wrote in message
    news:%...

    > 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... :)
     
    Mark Rae, Oct 29, 2005
    #12
  13. 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.




    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" <> wrote in message
    news:...
    > "Juan T. Llibre" <> wrote in message
    > news:%...
    >
    >> 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, Oct 29, 2005
    #13
  14. 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

    "Mark Rae" <> wrote in message
    news:...
    > "John Timney ( MVP )" <> wrote in message
    > news:...
    >
    >> 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.
    >
     
    John Timney \( MVP \), Oct 29, 2005
    #14
  15. JonnyD

    Greg Burns Guest

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


    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.

    :)
     
    Greg Burns, Oct 30, 2005
    #15
  16. 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.



    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/
    ======================================
    "Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
    news:...
    >> 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.

    >
    > 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, Oct 30, 2005
    #16
  17. JonnyD

    Paul Clement Guest

    On 28 Oct 2005 15:22:32 -0700, "JonnyD" <> wrote:

    ¤ 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)
     
    Paul Clement, Oct 31, 2005
    #17
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Lee
    Replies:
    2
    Views:
    3,206
    Charles Bailey
    May 9, 2004
  2. =?Utf-8?B?cFN0YW4=?=
    Replies:
    1
    Views:
    542
    Ken Cox [Microsoft MVP]
    Feb 23, 2005
  3. Monty
    Replies:
    14
    Views:
    759
    Walter Wang [MSFT]
    Jun 22, 2007
  4. ABCL
    Replies:
    0
    Views:
    563
  5. csthompsondo
    Replies:
    0
    Views:
    724
    csthompsondo
    Feb 27, 2011
Loading...

Share This Page