Using Access Database while Internet Users use DB simultaneously

Discussion in 'ASP .Net' started by Marcus S, Jan 22, 2011.

  1. Marcus S

    Marcus S Guest

    I have an msAccess database back-end ( .mdb or .accdb both formats
    work ) set up with .asp pages. An msAccess front-end links to the
    msAccess back-end. The front-end is what my co-workers will use to
    edit the database. With the .asp pages web users are able to access
    the back-end at the same time the msAccess front-end is being used,
    its great. I am trying to convert to .aspx and its works great on the
    web-side as long as the msAccess front-end is not open, otherwise I
    get a "file already in use error" from the .aspx page.
    The .asp, .aspx, and the ( .mdb or .accdb [format not the issue
    here] ) files are located in the same directory with the same
    permissions (including IIS_WPG and IUSR) where a simultaneously
    accessible .asp / ( .mdb or .accdb ) solution is actively working. How
    but once I make it an .aspx / ( .mdb or .accdb ) combo it is only
    accessible by .aspx while no msAccess users are using the ( .mdb
    or .accdb ). So what sort of record locking (.ldb / .lccdb) enabler
    is missing from my .aspx implementation?
     
    Marcus S, Jan 22, 2011
    #1
    1. Advertising

  2. Marcus S

    Arne Vajhøj Guest

    On 21-01-2011 19:23, Marcus S wrote:
    > I have an msAccess database back-end ( .mdb or .accdb both formats
    > work ) set up with .asp pages. An msAccess front-end links to the
    > msAccess back-end. The front-end is what my co-workers will use to
    > edit the database. With the .asp pages web users are able to access
    > the back-end at the same time the msAccess front-end is being used,
    > its great. I am trying to convert to .aspx and its works great on the
    > web-side as long as the msAccess front-end is not open, otherwise I
    > get a "file already in use error" from the .aspx page.
    > The .asp, .aspx, and the ( .mdb or .accdb [format not the issue
    > here] ) files are located in the same directory with the same
    > permissions (including IIS_WPG and IUSR) where a simultaneously
    > accessible .asp / ( .mdb or .accdb ) solution is actively working. How
    > but once I make it an .aspx / ( .mdb or .accdb ) combo it is only
    > accessible by .aspx while no msAccess users are using the ( .mdb
    > or .accdb ). So what sort of record locking (.ldb / .lccdb) enabler
    > is missing from my .aspx implementation?


    How does the connection string look in ASP and in ASP.NET?

    I believe there are differences between IIS/ASP.NET versions,
    but at least in some cases ASP run as IUSR_xxxx while ASP.NET runs
    as ASPNET !

    Arne
     
    Arne Vajhøj, Jan 22, 2011
    #2
    1. Advertising

  3. Marcus S

    Marcus S Guest

    On Jan 21, 7:08 pm, Arne Vajhøj <> wrote:
    > On 21-01-2011 19:23, Marcus S wrote:
    >
    >
    >
    >
    >
    >
    >
    >
    >
    > > I have an msAccess database back-end ( .mdb or .accdb both formats
    > > work ) set up with .asp pages.  An msAccess front-end links to the
    > > msAccess back-end.  The front-end is what my co-workers will use to
    > > edit the database.  With the .asp pages web users are able to access
    > > the back-end at the same time the msAccess front-end is being used,
    > > its great.  I am trying to convert to .aspx and its works great on the
    > > web-side as long as the msAccess front-end is not open, otherwise I
    > > get a "file already in use error" from the .aspx page.
    > > The .asp, .aspx, and the ( .mdb or .accdb [format not the issue
    > > here] ) files are located in the same directory with the same
    > > permissions (including IIS_WPG and IUSR) where a simultaneously
    > > accessible .asp / ( .mdb or .accdb ) solution is actively working. How
    > > but once I make it an .aspx /  ( .mdb or .accdb ) combo it is only
    > > accessible by .aspx while no msAccess users are using the ( .mdb
    > > or .accdb ).  So what sort of record locking (.ldb / .lccdb) enabler
    > > is missing from my .aspx implementation?

    >
    > How does the connection string look in ASP and in ASP.NET?
    >
    > I believe there are differences between IIS/ASP.NET versions,
    > but at least in some cases ASP run as IUSR_xxxx while ASP.NET runs
    > as ASPNET !
    >
    > Arne


    ..asp Connection String:
    sConnString="PROVIDER=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & Server.MapPath("xxxxxxx.mdb")

    ..aspx Connection String:
    sConnString="PROVIDER=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & Server.MapPath("xxxxxxx.mdb")


    So connection strings are identical.

    IIS/ASP.NET version is 2.0

    Could not find "ASPNET" user on my domain.
     
    Marcus S, Jan 22, 2011
    #3
  4. Marcus S

    Arne Vajhøj Guest

    On 22-01-2011 14:21, Marcus S wrote:
    > On Jan 21, 7:08 pm, Arne Vajhøj<> wrote:
    >> On 21-01-2011 19:23, Marcus S wrote:
    >>> I have an msAccess database back-end ( .mdb or .accdb both formats
    >>> work ) set up with .asp pages. An msAccess front-end links to the
    >>> msAccess back-end. The front-end is what my co-workers will use to
    >>> edit the database. With the .asp pages web users are able to access
    >>> the back-end at the same time the msAccess front-end is being used,
    >>> its great. I am trying to convert to .aspx and its works great on the
    >>> web-side as long as the msAccess front-end is not open, otherwise I
    >>> get a "file already in use error" from the .aspx page.
    >>> The .asp, .aspx, and the ( .mdb or .accdb [format not the issue
    >>> here] ) files are located in the same directory with the same
    >>> permissions (including IIS_WPG and IUSR) where a simultaneously
    >>> accessible .asp / ( .mdb or .accdb ) solution is actively working. How
    >>> but once I make it an .aspx / ( .mdb or .accdb ) combo it is only
    >>> accessible by .aspx while no msAccess users are using the ( .mdb
    >>> or .accdb ). So what sort of record locking (.ldb / .lccdb) enabler
    >>> is missing from my .aspx implementation?

    >>
    >> How does the connection string look in ASP and in ASP.NET?
    >>
    >> I believe there are differences between IIS/ASP.NET versions,
    >> but at least in some cases ASP run as IUSR_xxxx while ASP.NET runs
    >> as ASPNET !

    >
    > .asp Connection String:
    > sConnString="PROVIDER=Microsoft.ACE.OLEDB.12.0;"& _
    > "Data Source="& Server.MapPath("xxxxxxx.mdb")
    >
    > .aspx Connection String:
    > sConnString="PROVIDER=Microsoft.ACE.OLEDB.12.0;"& _
    > "Data Source="& Server.MapPath("xxxxxxx.mdb")
    >
    > So connection strings are identical.
    >
    > IIS/ASP.NET version is 2.0
    >
    > Could not find "ASPNET" user on my domain.


    Those connection strings are literally identical.

    ASPNET is a local account.

    I checked the docs - it should only be there for
    Win 2000 & XP IIS 5.x - newer versions should
    use the network service account.

    But I don't know if that is relevant for your problem.

    Arne
     
    Arne Vajhøj, Jan 23, 2011
    #4
  5. Marcus S

    Marcus S Guest

    On Jan 22, 5:29 pm, Arne Vajhøj <> wrote:
    > On 22-01-2011 14:21, Marcus S wrote:
    >
    >
    >
    >
    >
    >
    >
    >
    >
    > > On Jan 21, 7:08 pm, Arne Vajh j<>  wrote:
    > >> On 21-01-2011 19:23, Marcus S wrote:
    > >>> I have an msAccess database back-end ( .mdb or .accdb both formats
    > >>> work ) set up with .asp pages.  An msAccess front-end links to the
    > >>> msAccess back-end.  The front-end is what my co-workers will use to
    > >>> edit the database.  With the .asp pages web users are able to access
    > >>> the back-end at the same time the msAccess front-end is being used,
    > >>> its great.  I am trying to convert to .aspx and its works great on the
    > >>> web-side as long as the msAccess front-end is not open, otherwise I
    > >>> get a "file already in use error" from the .aspx page.
    > >>> The .asp, .aspx, and the ( .mdb or .accdb [format not the issue
    > >>> here] ) files are located in the same directory with the same
    > >>> permissions (including IIS_WPG and IUSR) where a simultaneously
    > >>> accessible .asp / ( .mdb or .accdb ) solution is actively working. How
    > >>> but once I make it an .aspx /  ( .mdb or .accdb ) combo it is only
    > >>> accessible by .aspx while no msAccess users are using the ( .mdb
    > >>> or .accdb ).  So what sort of record locking (.ldb / .lccdb) enabler
    > >>> is missing from my .aspx implementation?

    >
    > >> How does the connection string look in ASP and in ASP.NET?

    >
    > >> I believe there are differences between IIS/ASP.NET versions,
    > >> but at least in some cases ASP run as IUSR_xxxx while ASP.NET runs
    > >> as ASPNET !

    >
    > > .asp Connection String:
    > > sConnString="PROVIDER=Microsoft.ACE.OLEDB.12.0;"&  _
    > > "Data Source="&  Server.MapPath("xxxxxxx.mdb")

    >
    > > .aspx Connection String:
    > > sConnString="PROVIDER=Microsoft.ACE.OLEDB.12.0;"&  _
    > > "Data Source="&  Server.MapPath("xxxxxxx.mdb")

    >
    > > So connection strings are identical.

    >
    > > IIS/ASP.NET version is 2.0

    >
    > > Could not find "ASPNET" user on my domain.

    >
    > Those connection strings are literally identical.
    >
    > ASPNET is a local account.
    >
    > I checked the docs - it should only be there for
    > Win 2000 & XP IIS 5.x - newer versions should
    > use the network service account.
    >
    > But I don't know if that is relevant for your problem.
    >
    > Arne


    I'm running server 2003 64-bit with IIS 6.0 so it was the network
    service account.

    Thank you, I have solved this problem with your help.

    http://msdn.microsoft.com/en-us/library/ff647402.aspx

    FYI: Posting the above link for others in the future. It got my aspx
    executing while msAccess was also open. thanks again, Arne.
     
    Marcus S, Jan 23, 2011
    #5
  6. Marcus S

    Brian Cryer Guest

    On 22/01/2011 00:23, Marcus S wrote:
    > I have an msAccess database back-end ( .mdb or .accdb both formats
    > work ) set up with .asp pages. An msAccess front-end links to the
    > msAccess back-end. The front-end is what my co-workers will use to
    > edit the database. With the .asp pages web users are able to access
    > the back-end at the same time the msAccess front-end is being used,
    > its great. I am trying to convert to .aspx and its works great on the
    > web-side as long as the msAccess front-end is not open, otherwise I
    > get a "file already in use error" from the .aspx page.
    > The .asp, .aspx, and the ( .mdb or .accdb [format not the issue
    > here] ) files are located in the same directory with the same
    > permissions (including IIS_WPG and IUSR) where a simultaneously
    > accessible .asp / ( .mdb or .accdb ) solution is actively working. How
    > but once I make it an .aspx / ( .mdb or .accdb ) combo it is only
    > accessible by .aspx while no msAccess users are using the ( .mdb
    > or .accdb ). So what sort of record locking (.ldb / .lccdb) enabler
    > is missing from my .aspx implementation?


    Sorry, I can't answer your specific question :(

    But, when I'm recruiting and interviewing software engineers its one of
    my standard questions to ask why is MS-Access not suitable as a database
    for a website. The bottom line is that MS-Access (which is great for
    what it is) isn't suitable for a website becasue its single threaded,
    meaning that if one process is doing a query or an insert then it blocks
    everything else. So its fine for developing a simple site with (not that
    I would) and I suppose for very low traffic sites, but not it you have
    more than a small handful of concurrent visitors. So in the mid to long
    term you would experience much better performance by switching to
    Sql-Server or MySQL.

    With your problem in hand, what is the error you are getting or is it
    just locking up? If its locking then its almost certainly down to the
    single threaded nature of MS-Access - so check to ensure that all your
    connections are closed and you haven't left any queries open. If you get
    an error then what it is?

    Hope this helps.
    --
    Brian Cryer
    http://www.cryer.co.uk/brian


    --
    Brian Cryer
    http://www.cryer.co.uk
     
    Brian Cryer, Jan 24, 2011
    #6
  7. Marcus S

    Jason Keats Guest

    Brian Cryer wrote:
    >
    > Sorry, I can't answer your specific question :(
    >
    > But, when I'm recruiting and interviewing software engineers its one of
    > my standard questions to ask why is MS-Access not suitable as a database
    > for a website. The bottom line is that MS-Access (which is great for
    > what it is) isn't suitable for a website becasue its single threaded,
    > meaning that if one process is doing a query or an insert then it blocks
    > everything else. So its fine for developing a simple site with (not that
    > I would) and I suppose for very low traffic sites, but not it you have
    > more than a small handful of concurrent visitors. So in the mid to long
    > term you would experience much better performance by switching to
    > Sql-Server or MySQL.
    >


    Brian, a Jet database can have up to 255 concurrent users. If you don't
    maintain a database connection for each user, then it is possible to
    support many more concurrent users.

    The judicious use of disconnected ADO recordsets, to help minimise
    connection time, along with ASP.NET's ability to queue requests make it
    quite possible to support several hundred concurrent users - which is as
    many as most ASP.NET sites can support anyway.

    However, it is true that MSSQL (or any another true database server) is
    likely to perform better and to be more reliable - due to it's log
    files, etc.
     
    Jason Keats, Jan 24, 2011
    #7
  8. Marcus S

    Brian Cryer Guest

    On 24/01/2011 13:57, Jason Keats wrote:
    > Brian Cryer wrote:
    >>
    >> Sorry, I can't answer your specific question :(
    >>
    >> But, when I'm recruiting and interviewing software engineers its one of
    >> my standard questions to ask why is MS-Access not suitable as a database
    >> for a website. The bottom line is that MS-Access (which is great for
    >> what it is) isn't suitable for a website becasue its single threaded,
    >> meaning that if one process is doing a query or an insert then it blocks
    >> everything else. So its fine for developing a simple site with (not that
    >> I would) and I suppose for very low traffic sites, but not it you have
    >> more than a small handful of concurrent visitors. So in the mid to long
    >> term you would experience much better performance by switching to
    >> Sql-Server or MySQL.
    >>

    >
    > Brian, a Jet database can have up to 255 concurrent users. If you don't
    > maintain a database connection for each user, then it is possible to
    > support many more concurrent users.


    True. Although I still think that Access is single threaded - but I
    acknowledge that I confused the boundaries between Jet (driver) and
    Access (application).

    > The judicious use of disconnected ADO recordsets, to help minimise
    > connection time, along with ASP.NET's ability to queue requests make it
    > quite possible to support several hundred concurrent users - which is as
    > many as most ASP.NET sites can support anyway.
    >
    > However, it is true that MSSQL (or any another true database server) is
    > likely to perform better and to be more reliable - due to it's log
    > files, etc.


    Agreed.

    I don't suppose Microsoft have made any progress on allowing space to be
    reclaimed, to avoid the need for a compact operation?
    --
    Brian Cryer
    http://www.cryer.co.uk
     
    Brian Cryer, Jan 24, 2011
    #8
  9. Marcus S

    Arne Vajhøj Guest

    On 24-01-2011 04:23, Brian Cryer wrote:
    > But, when I'm recruiting and interviewing software engineers its one of
    > my standard questions to ask why is MS-Access not suitable as a database
    > for a website. The bottom line is that MS-Access (which is great for
    > what it is) isn't suitable for a website becasue its single threaded,
    > meaning that if one process is doing a query or an insert then it blocks
    > everything else.


    I can not but wonder about what you hire.

    The above is not correct.

    The LDB file is specifically there to handle that problem.

    > So its fine for developing a simple site with (not that
    > I would) and I suppose for very low traffic sites, but not it you have
    > more than a small handful of concurrent visitors. So in the mid to long
    > term you would experience much better performance by switching to
    > Sql-Server or MySQL.


    SQLServer and MySQL can handle a lot more concurrent users. LDB file
    works but is not as efficient as the in memory locks in a database
    server.

    Arne
     
    Arne Vajhøj, Jan 25, 2011
    #9
  10. Marcus S

    Arne Vajhøj Guest

    On 24-01-2011 10:46, Brian Cryer wrote:
    > On 24/01/2011 13:57, Jason Keats wrote:
    >> Brian Cryer wrote:
    >>> But, when I'm recruiting and interviewing software engineers its one of
    >>> my standard questions to ask why is MS-Access not suitable as a database
    >>> for a website. The bottom line is that MS-Access (which is great for
    >>> what it is) isn't suitable for a website becasue its single threaded,
    >>> meaning that if one process is doing a query or an insert then it blocks
    >>> everything else. So its fine for developing a simple site with (not that
    >>> I would) and I suppose for very low traffic sites, but not it you have
    >>> more than a small handful of concurrent visitors. So in the mid to long
    >>> term you would experience much better performance by switching to
    >>> Sql-Server or MySQL.
    >>>

    >>
    >> Brian, a Jet database can have up to 255 concurrent users. If you don't
    >> maintain a database connection for each user, then it is possible to
    >> support many more concurrent users.

    >
    > True. Although I still think that Access is single threaded


    It is not. If that was the case then the LDB file would
    not be needed.

    Arne
     
    Arne Vajhøj, Jan 25, 2011
    #10
  11. Marcus S

    Brian Cryer Guest

    On 25/01/2011 01:15, Arne Vajhøj wrote:
    > On 24-01-2011 10:46, Brian Cryer wrote:
    >> On 24/01/2011 13:57, Jason Keats wrote:
    >>> Brian Cryer wrote:
    >>>> But, when I'm recruiting and interviewing software engineers its one of
    >>>> my standard questions to ask why is MS-Access not suitable as a
    >>>> database
    >>>> for a website. The bottom line is that MS-Access (which is great for
    >>>> what it is) isn't suitable for a website becasue its single threaded,
    >>>> meaning that if one process is doing a query or an insert then it
    >>>> blocks
    >>>> everything else. So its fine for developing a simple site with (not
    >>>> that
    >>>> I would) and I suppose for very low traffic sites, but not it you have
    >>>> more than a small handful of concurrent visitors. So in the mid to long
    >>>> term you would experience much better performance by switching to
    >>>> Sql-Server or MySQL.
    >>>>
    >>>
    >>> Brian, a Jet database can have up to 255 concurrent users. If you don't
    >>> maintain a database connection for each user, then it is possible to
    >>> support many more concurrent users.

    >>
    >> True. Although I still think that Access is single threaded

    >
    > It is not. If that was the case then the LDB file would
    > not be needed.
    >
    > Arne


    When you run a query in Access you can't then run a second query until
    the results of the first have come back. That's because its single threaded.

    This is a separate issue from whether you can open two separate
    instances - as Jason has already covered.
    --
    Brian Cryer
    http://www.cryer.co.uk
     
    Brian Cryer, Jan 25, 2011
    #11
  12. Marcus S

    Brian Cryer Guest

    On 25/01/2011 01:14, Arne Vajhøj wrote:
    > On 24-01-2011 04:23, Brian Cryer wrote:
    >> But, when I'm recruiting and interviewing software engineers its one of
    >> my standard questions to ask why is MS-Access not suitable as a database
    >> for a website. The bottom line is that MS-Access (which is great for
    >> what it is) isn't suitable for a website becasue its single threaded,
    >> meaning that if one process is doing a query or an insert then it blocks
    >> everything else.

    >
    > I can not but wonder about what you hire.
    >
    > The above is not correct.


    Yes, I've been corrected on that by Jason.

    What I actually ask is why Access isn't suitable as a back end database
    for a website. I was wrong on the locking issue but this is only part of
    the answer.

    > The LDB file is specifically there to handle that problem.
    >
    >> So its fine for developing a simple site with (not that
    >> I would) and I suppose for very low traffic sites, but not it you have
    >> more than a small handful of concurrent visitors. So in the mid to long
    >> term you would experience much better performance by switching to
    >> Sql-Server or MySQL.

    >
    > SQLServer and MySQL can handle a lot more concurrent users. LDB file
    > works but is not as efficient as the in memory locks in a database
    > server.


    Its not just locks which makes Access a poor choice. Its less resilient,
    doesn't scale well and needs to be compacted (i.e. downtime), and I'm
    sure if you think about it you should be able to add to that list.

    Access is a good choice in some scenarios (it even has some advantages
    over SQL-Server and MySQL), but I wouldn't advocate its use for a website.
    --
    Brian Cryer
    http://www.cryer.co.uk
     
    Brian Cryer, Jan 25, 2011
    #12
    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. Farooq Khan
    Replies:
    2
    Views:
    419
    Joseph E Shook [MVP - ADSI]
    Oct 1, 2004
  2. Siegfried Heintze
    Replies:
    1
    Views:
    783
    Paul Henderson
    Apr 15, 2006
  3. Alper Özgür
    Replies:
    0
    Views:
    478
    Alper Özgür
    May 15, 2006
  4. Sean Davis
    Replies:
    16
    Views:
    915
    Dennis Lee Bieber
    Jan 12, 2007
  5. kanchan
    Replies:
    1
    Views:
    834
    Karthik Kumar
    Nov 13, 2004
Loading...

Share This Page