none
Backend database locked by changing report in front end from 'Report View' to 'Design View' RRS feed

  • Question

  • Hi,

    We have front and backend files. The backend is on network drive and it is accessed by front-end users as well as web site.

    Everything started with errors in IIS logs: 'The_database_has_been_placed_in_a_state_by_user_'Admin'_on_machine_'

    Somehow the backend database file is locked by front end users; they have linked tables and some forms and reports.

    We started troubleshooting this problem and noticed that there is lag when users are switching from design to report view in the front end; sometimes it is over 1 min even for report based on single table.  During this 'switching' time looks like access is changing lock on backend database file causing web errors.

    Is there anything we can do to prevent this locking? 

    I inherited these databases and looks like they are Access2003.

    Thank you,

    George



    • Edited by GeorgePrz Wednesday, April 26, 2017 2:52 PM
    Tuesday, April 25, 2017 9:34 PM

All replies

  • To check whether it is specific to Access 2003, I suggest you make a test with newer version like Access 2016.

    >> Everything started with errors in IIS logs

    Did you configure anything to use errors in IIS logs? I am wondering why Access database throws errors in IIS.

    >> there is lag when users are switching from design to report view in the front end

    If they open Report View directly, will this issue happen?

    If you link a table from Access to SQL Server, will this issue exist?


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 27, 2017 7:44 AM
  • Thank you for reply,

    We have web site using Access database.  We are opening and closing recorsets there, that is why we saw errors in IIS logs.  Our site is still in classic ASP, so if I add On Error Resume Next in VB when working with recordsets I'm getting script timeout errors probably because of the db locks.  If I don't have On Error Resume Next, web users will get 500 Error on the site immediately.

    I'll try to convert 2003 to 2016 to test this problem, but we still have few front End users using Access 2003, so I'm not sure if 2016 in the backend will work for them.  I'll try it.

    About opening the Report View directly - this seems to be working fine; from testing looks like lock is happening when front end users are switching between design and view mode.

    We have phase 2 of the project we are working on where we are upsizing Access to SQL; right now I don't have working copies of website and front end to test it.  I think the issue will go away when we upsize, but we have few months to go with current setup...

    I found one recommendation on the web suggesting that in front end Access apps open connection to backend and keep it open all the time for duration of the front end session.  I tried that and I noticed big performance improvement.  Switching reports time from View to Design went down from over 4 min to under 5 sec.  Looks like this is the window when locks are happening, so it is much shorter reducing our chances of conflicts.  This will not eliminate the errors we see, but will make the window much shorter.

    George

    Thursday, April 27, 2017 3:09 PM
  • >>I'll try to convert 2003 to 2016 to test this problem, but we still have few front End users using Access 2003, so I'm not sure if 2016 in the backend will work for them. 

    If you have any update about this, please feel free to let us know.

    >>this seems to be working fine; from testing looks like lock is happening when front end users are switching between design and view mode.

    Is the developer or end user to switch between design and view mode? If this is end user, I would suggest you forbid them to switch.  Normally, end user uses the report to show records, they should not switch to design mode to change report.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 28, 2017 5:53 AM
  • Hi Edward,

    I moved all tables to 2010-2013 file, but operations from front end seems slower and I can still see the same error in logs.

    I asked person responsible for front end app to lock it down so users cannot go to design view of forms/reports.  He said he did it, but we still see the same error in IIS logs.

    So I took a look at the classic asp site, maybe errors we see are wrong and misleading...  Most of the pages we see in the IIS logs have multiple recordsets opening and closing connections.  The code is like:

    RSSub.Open strQuery, strConstConnect, 3, 1

    To make it worst some of the recordsets are inside loops.  I changed the code to open one connection per page and then use connection object for all recordsets, like:

    Set cn = Server.CreateObject("ADODB.Connection")
    cn.Open strConstConnect

    RSSub.Open strQuery, cn, 3, 1

    We'll deploy this change tomorrow to see if it helps.

    I'll keep you posted.

    Thank you,

    George

    Wednesday, May 3, 2017 4:20 PM
  • >>He said he did it, but we still see the same error in IIS logs

    It seems it is not related with changing view in Access database.

    If you have any update about your issue, please feel free to let us know.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 8, 2017 6:01 AM