none
Access 2003 frontend linked to SQL 2005 backend hanging with multiple users HELP!! RRS feed

  • Question

  • I have an access 2003 complex db that I recently upsized the backend to SQL 2005.  It all works great up to five users (each with their own frontend) then it starts getting weird.  Dropdowns lose their connection, etc.  Is there some file limitations on the access side or as I believe is access creating blocking locks on the SQL side.  I am very frustrated because I have been working on this problem for over a month and every time I think I have it solved and I can load 15 instances there seem to be problems.   Are there some settings I can change?  Any ideas would be greatly appreciated.  Thanks, Chris
    Tuesday, November 30, 2010 3:03 PM

Answers

  • Hi Chris,
     
    This sounds like locking problems.  If you're using all linked tables,
    then there is additional overhead that can be happening, especially
    with that many records and concurrent users.  In SSMS, are you seeing
    blocked processes and excessive locks?
     
    For your large comboboxes and large read-only list forms, you can use
    passthrough queries with the NOLOCK hint.  This will cut down greatly
    on locks.
     
    Do your tables include a RowVersion (aka TimeStamp) field?  This can
    help with concurrency performance, but there are pros and cons, and
    special considerations if you have processes that sweep through and
    update records in the background.
     
    I cover all of this in my PowerPoint presentation on techniques for
    using Access as a client-server front-end to SQL Server databases.
    It's called "Best of Both Worlds" at our free J Street Downloads page:
    http://ow.ly/M2WI.  It includes some thoughts on when to use SQL
    Server, performance and security considerations, concurrency
    approaches, and techniques to help everything run smoothly.
     
    Armen Stein
    Microsoft Access MVP
    www.JStreetTech.com
     
     
    • Proposed as answer by Bruce Song Monday, December 6, 2010 7:18 AM
    • Marked as answer by Bruce Song Monday, December 13, 2010 2:10 AM
    Tuesday, November 30, 2010 7:09 PM

All replies

  • hi christine,

    so you have created a front-end with linked tables to your SQL Server 2005. Is it a SQL Server 2005 Express or higher?

    Check whether you're have set the database to auto-close (in its properties in the SSMSE).

    A user limit does not exist either in SQL Server 2005 Express+ or on the Access side, especially as you're using a separate front-end file for each user.

    What do you mean with "Dropdowns lose their connection"?


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Tuesday, November 30, 2010 3:49 PM
  • hi christine,

    btw, forgot to mention: have you installed the latest service pack for Office? Have installed the hotfixes after the last service pack?

    http://support.microsoft.com/kb/945674

    Have you fully patched your SQL Server instance?


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Tuesday, November 30, 2010 3:51 PM
  • Hi Stefan,

    I'm running enterprise edition and yes I have set the autoclose to yes.  I will download the service pack for office but I usually have the most current packs autodownloaded.  I'm don't understand what fully patched you sql server instance means.

    What I mean by dropping off is that I have several dropdowns on my tabbed notebook form and the dropdowns stop running and freeze the system totally or when they come back there are no records.  The dropdowns have simple queries using two tables.  I tried to create views to accomplish what I wanted to do and it was worse.  I finally created a table for the dropdown that I maintain through a trigger.  The other problems is that after I load about 10 instances of the application it freezes in lots of places not even the same place all the time.   At times the only way to get out is to close the access app.

    this application has about 60 tables and some have about 2 million records.  I have a system dsn odbc link to my sql and have the links in my access front end. 

    This problem has my client about ready to kill me every time we try to go live with the SQL backend version.

    Thanks,

    Chris

    Tuesday, November 30, 2010 6:02 PM
  • Christine,

    You might want to think about asking their network/hardware folks about how they have things configured - are there any bandwidth limits in place on their routers and such - are you certain that all the SQL Server IP ports are unblocked? This sounds like there is a network or some other  service-level-imposed limit that you're hitting when you shouldn't be. It's certainly nothing that you should need to bend over backwards to handle well with the configuration you've told us about. Are you certain that there aren't any CALs/licensing issues involved? Who set up the server and what options were selected for licensing et. al.?

    What's the SQL Server server hardware & OS version? (thinking about what you could even possibly be running into - casting a wide net to start with)


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)
    Tuesday, November 30, 2010 6:15 PM
  • Hi Chris,
     
    This sounds like locking problems.  If you're using all linked tables,
    then there is additional overhead that can be happening, especially
    with that many records and concurrent users.  In SSMS, are you seeing
    blocked processes and excessive locks?
     
    For your large comboboxes and large read-only list forms, you can use
    passthrough queries with the NOLOCK hint.  This will cut down greatly
    on locks.
     
    Do your tables include a RowVersion (aka TimeStamp) field?  This can
    help with concurrency performance, but there are pros and cons, and
    special considerations if you have processes that sweep through and
    update records in the background.
     
    I cover all of this in my PowerPoint presentation on techniques for
    using Access as a client-server front-end to SQL Server databases.
    It's called "Best of Both Worlds" at our free J Street Downloads page:
    http://ow.ly/M2WI.  It includes some thoughts on when to use SQL
    Server, performance and security considerations, concurrency
    approaches, and techniques to help everything run smoothly.
     
    Armen Stein
    Microsoft Access MVP
    www.JStreetTech.com
     
     
    • Proposed as answer by Bruce Song Monday, December 6, 2010 7:18 AM
    • Marked as answer by Bruce Song Monday, December 13, 2010 2:10 AM
    Tuesday, November 30, 2010 7:09 PM
  • Thanks all.  I agree with Armen that I am having a locking problem.   I found your slide show very good.  In attempting to use pass through queries I ran into some problems.  Such as password connection requests.  I am going to incorporate some of your techniques to see if I can get this thing off the ground.  There are approximately 30 end users on this system and I need it to work soon.  Should I possibly be looking at ado connections?  I'm kind of new to these problems.  I have done two other conversions like this in the past and had no problems but much less data and much fewer users.  thanks

     

    Chris

    Tuesday, November 30, 2010 8:19 PM
  • Hi Chris,
     
    Sounds like you're using SQL authentication, not Windows.  You need to
    store the UID and password in the connect strings of the passthrough
    queries.  However, as you've found sometimes Access will prompt for
    credentials even when you've already provided them.
     
    We use a technique where we open a Global DAO recordset on a linked
    table (just a one-record configuration table, doesn't matter which
    one) and leave it open for the duration of the application.  This
    prevents any further prompting for credentials.
     
    Armen Stein
    Microsoft Access MVP
    www.JStreetTech.com
     
     
    Tuesday, November 30, 2010 10:40 PM
  • Hi Armen,

    Okay, now I have all of my dropdowns using passthrough queries with nolock.  The curious thing is if I run the pt query by itself at the database level or use it directly as the dropdown rowsource it still seems to create several locks.  However, if I use it in an access query for the dropdown the locks are diminished.  That's fine but just seems curious.

    The way I have the system set up already is that they select their records by a dropdown (similar to your index) that requeries the recordsource for just that record.  I'm not sure if it will be a problem but when I have a subform with multiple records with a couple of tables in the query (that is done as a snapshot with nolocks - these records are not going to be edited) many page locks are set in SQL.  I have tried to do a passthrough query for this and just reset the sourcesql on the fly but it still won't let me use the passthrough.  Would it be a good idea to create this as a view with nolocks and then link the view into the db? 

    I also have a question as to your reference to setting the rowsource to qsptcboQUERYNAME.  Is that just supposed to be the passthrough queryname?

    Any ideas would be greatly appreciated.

    Christine

    Wednesday, December 8, 2010 7:59 PM
  • On Wed, 8 Dec 2010 19:59:24 +0000, christine_s wrote:
     
    >Okay, now I have all of my dropdowns using passthrough queries with nolock.  The curious thing is if I run the pt query by itself at the database level or use it directly as the dropdown rowsource it still seems to create several locks.  However, if I use it in an access query for the dropdown the locks are diminished.  That's fine but just seems curious.
     
    I'm not sure why there would be a difference in locks.  All I can
    think is that Access might be managing the cache of records it
    retrieves differently in each scenario.  It probably won't be an
    issue.  In these situations, unless you're actually seeing a problem,
    it's best not to stare at it too closely.  :)
     
    >The way I have the system set up already is that they select their records by a dropdown (similar to your index) that requeries the recordsource for just that record.  I'm not sure if it will be a problem but when I have a subform with multiple records with a couple of tables in the query (that is done as a snapshot with nolocks - these records are not going to be edited) many page locks are set in SQL.  I have tried to do a passthrough query for this and just reset the sourcesql on the fly but it still won't let me use the passthrough.  Would it be a good idea to create this as a view with nolocks and then link the view into the db? 
     
    As you know, subforms with Master/Child can't be based on passthrough
    queries.  Using normal Access linked tables for these is usually fine,
    even though more locks are placed, if you follow the best practice of
    limiting your main form to just a single record.  We have tons of
    Access-SQL apps that use this approach.
     
    Your alternate approach is a good one too - you can base forms and
    subforms on views, which are more development effort but can
    definitely be more efficient.  We often use views for reports and
    subreports too.
     
    >I also have a question as to your reference to setting the rowsource to qsptcboQUERYNAME.  Is that just supposed to be the passthrough queryname?
     
    Yes, we use "qspt" (Query Sql PassThrough) as the prefix for
    passthrough queries, and then "cbo" to indicate that this query is
    only used as a rowsource for a combobox.  If you want to use a
    different naming convention, that's fine!  Just use one.  :)
     
    Cheers,
    Armen
     
    Armen Stein
    Microsoft Access MVP
    www.JStreetTech.com
     
     
    Thursday, December 9, 2010 7:39 PM
  • Hi Armen,  Thanks so much for your time and input.

    Christine

    Friday, December 10, 2010 5:12 PM
  • Hi Armen,

    Moving along quite well with your help.  I had 22 instances open before my memory went out and all seemed to be running fine.  Have to test at client yet but have my fingers crossed.  Have a few other questions for you. 

    1.  Is there a way to query sql that is like the no lock but tells mssql that you will not be editing the records at all (similar to a snapshot).  My forms have edits set to no but I'm not sure if mssql is placing any locks that may conflict.

    2.  I see an object id in the ms sql activity monitor.  Is there a way to tie that back to objects on my form?

    3.  Is it important that I change all my report queries to pass through's with nolock?  Right now I have a report engine I wrote that is based on a table with sql in a field that my clients can change on the fly.  There are over 200 reports that will have to be addressed if this is the case.  How does mssql handle report requests with regard to locks? 

    I downloaded the chart that shows which locks play nicely together but due to the amount of traffic on this db it is quite difficult to tell where there may be a conflict.  Also is there a way to see the locks actively happen without using the the activity monitor which seems to be a snapshot of the locks and needs to be refreshed to see them.

    As you might be able to tell, I'm just a little gun shy at this point in time.  I have tried to go live with this project 3 times and when either multiple users are on or maybe when lots of action by each is taking place I'm getting ODBC can't load errors.

    On my most problematic dropdowns I even set the rowsource to "" when the user exits off.

    I don't know how you feel about this, but if I run into more problems would you mind if I contact you directly?

    Thanks again,

    Christine

    Tuesday, December 14, 2010 7:26 PM