none
Reports in Access 2010 runtime are slow for other users RRS feed

  • Question

  • Hi

    We were using Access 2003 runtime, with an ADP created in Office 2003. Backend is MS SQL Server 2008 R2.

    In Office 2010, I created a new ADP and imported all the objects. There are two reports in the ADP, and they ran fine for me.

    I installed Access 2010 runtime and Service Pack 2. Used the runtime, and the reports ran quickly for me. However, the reports run very slowly for other users. I've logged on to their PCs, and the reports will run fine for me.

    I kept one PC with runtime 2003, and the reports run quickly on that for the other users and me. It just seems to be connected to the runtime access 2010.

    In summary - report takes 4 seconds for me, approx. 16 seconds for other users. On the same PC.

    How should I resolve this issue?

    Thanks

    Wednesday, February 24, 2016 11:03 AM

All replies

  • please clarify: "I've logged on to their PCs, and the reports will run fine for me. "

    how is it that the same PC appears to be operating differently?  Is this a terminal services deployment? 

    Wednesday, February 24, 2016 11:33 PM
  • Hi SimeonD,

    >> report takes 4 seconds for me, approx. 16 seconds for other users
    Did you run the same report with the same filter?

    >> However, the reports run very slowly for other users. I've logged on to their PCs, and the reports will run fine for me
    Did you mean there are multiple users in the same pcs, and only you run fine? Have other users installed Access 2010 runtime and Service Pack2?

    >> I kept one PC with runtime 2003, and the reports run quickly on that for the other users and me. It just seems to be connected to the runtime access 2010.
    Do you mean Office 2010 ADP run fine with runtime 2003 than runtime 2010? To check whether it is related with runtime 2010, I suggest you create a clean VM, install runtime 2010, and test with your Office 2010 ADP.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, February 25, 2016 3:11 AM
  • please clarify: "I've logged on to their PCs, and the reports will run fine for me. "

    how is it that the same PC appears to be operating differently?  Is this a terminal services deployment? 

    Its not a terminal services deployment.

    >> how is it that the same PC appears to be operating differently?

    That's exactly my question! :)

    Thursday, February 25, 2016 9:09 AM
  • >> report takes 4 seconds for me, approx. 16 seconds for other users
    >Did you run the same report with the same filter?

    Yes, exact same report. The report only runs against one client. So I just pick the same client.

    >> However, the reports run very slowly for other users. I've logged on to their PCs, and the reports will run fine for me
    >Did you mean there are multiple users in the same pcs, and only you run fine? Have other users >installed Access 2010 runtime and Service Pack2?

    Each user has one PC. Each PC has 2010 runtime and SP2, with Access 2010 ADP.

    I've logged on to the same PC, ran the same report and it runs quickly for me.

    >> I kept one PC with runtime 2003, and the reports run quickly on that for the other users and me. It just seems to be connected to the runtime access 2010.
    >Do you mean Office 2010 ADP run fine with runtime 2003 than runtime 2010? To check whether it is >related with runtime 2010, I suggest you create a clean VM, install runtime 2010, and test with your >Office 2010 ADP.

    Its definitely runtime 2010 related. The

    On a VM I tested the following for other users

    1. Office 2010 ADP with Runtime 2010 - slow
    2. Office 2003 ADP with Runtime 2010 - slow
    3. Office 2003 ADP with Runtime 2003 - Fast

    Note that it ran fast for me in all 3 cases.

     
    • Edited by SimeonD Thursday, February 25, 2016 9:41 AM Formatting
    Thursday, February 25, 2016 9:40 AM
  • Hi SimeonD,

    >> Each user has one PC. Each PC has 2010 runtime and SP2, with Access 2010 ADP. I've logged on to the same PC, ran the same report and it runs quickly for me.

    I am confused. Each user has one pc, if there is no multiple users in the same pcs, how did you log to the same pc? I do not know your situation that it works for you and not work for other users.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, February 29, 2016 2:33 AM
  • Hi SimeonD,

    >> Each user has one PC. Each PC has 2010 runtime and SP2, with Access 2010 ADP. I've logged on to the same PC, ran the same report and it runs quickly for me.

    I am confused. Each user has one pc, if there is no multiple users in the same pcs, how did you log to the same pc? I do not know your situation that it works for you and not work for other users.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Its simple - I got the user to log off the PC. Then I logged on.

    If you do not know the solution, can you escalate this please? I have an MSDN subscription, and am meant to get priority support.

    Monday, February 29, 2016 10:34 AM
  • Hi SimeonD,

    Do you and other users have the same permission? I will escalate this thread, and try to involve some senior engineers into this issue. It will take some time. Your patience will be greatly appreciated.

    Sorry for any inconvenience and have a nice day.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, March 1, 2016 2:08 AM
  • Hi Edward

    That would be great, thank you.

    To the best of my knowledge, I have the same permissions on the SQL side as the other users.

    Regards

    Wednesday, March 2, 2016 12:43 PM
  • Hi

    Is there any update on this issue?

    Regards

    Tuesday, March 8, 2016 4:35 PM
  • Hi SimeonD,

    I have escalated it, but there is no any updates on this issue. If there is any updates, senior engineers would post on this thread.

    Sorry for any inconvenience and have a nice day.

    If this is an urgent issue, I will suggest you contacting the Microsoft professional support so that our engineers can work closely with you to troubleshoot this issue.

    If the support engineer determines that the issue is the result of a bug the service request will be a no-charge case and you won't be charged. Please visit the below link to see the various paid support options that are available to better meet your needs. http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, March 17, 2016 5:17 AM
  • Hi Edward

    I have further information relating to this issue.

    If a user is setup with sysadmin role in SQL Server, then the reports will run quickly.

    If a user does have the sysadmin role, the report will run slowly.

    Note this happens in Access 2010, does not happen with Access 2003.

    Obviously I don't want to add users to the sysadmin role, so I need a way around it.

    Can you check if his sysadmin issue is a known bug with Access 2010 please. Or let me know a way around it.

    Thanks

    Wednesday, March 23, 2016 12:30 PM
  • Hi Edward

    I have further information relating to this issue.

    If a user is setup with sysadmin role in SQL Server, then the reports will run quickly.

    If a user does have the sysadmin role, the report will run slowly.

    Note this happens in Access 2010, does not happen with Access 2003.

    Obviously I don't want to add users to the sysadmin role, so I need a way around it.

    Can you check if his sysadmin issue is a known bug with Access 2010 please. Or let me know a way around it.

    Thanks

    That makes absolutely no sense to me. Whether a person has sysadmin rights or not, as long as they have read permissions on the tables involved it shouldn't matter. I've been using SQL Server as a back end for more than 15 years and have never run up against this oddity. This must be related to their profiles. Could it be they have a slower connection speed than your profile? Have your network guy look into the profiles.

    While it doesn't address your problem, I have one question: Why would you create a new ADP in 2010 when you still have to basically use the MDB file type? 2010 does not let you create ADPs under the 2010 format. Have you tried staying with your 2003 ADP?


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Wednesday, March 23, 2016 2:08 PM
  • Hi Bill

    Nothing to do with their profiles or network. Like I said, tick the Sysadmin box against any user, the reports will speed up immediately. Its that simple.

    I've seen loads of posts on this sysadmin issue, so its not just me. Unfortunately I've seen no solutions!

    For example

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c2538e59-ab8e-45ee-b35d-11863e71b5b2/sql-server-2008-sp1-and-access?forum=sqldatabaseengine

    To answer your other questions

    >Why would you create a new ADP in 2010 when you still have to basically use the MDB file type? 2010 > does not  let you create ADPs under the 2010 format.

    In Access 2010, just pick ADP from the dropdown list when you are entering the name for your new database. Or do you mean something different?

    >Have you tried staying with your 2003 ADP?

    Yes I have. I tried opening the 2003 ADP from within Access 2010 and the problem still applies. On both runtime and full versions for Access 2010.

    One new link I found is

    http://www.pcreview.co.uk/threads/adp-slow-because-sysreferences-cached-on-first-view-or-table-open.3832564/

    He suggests prohibiting select on the Sysreferences table. I can see the Sys.Sysreferences view, but I don't know how to get at the underlying table to change permissions. Any idea on how I would do this? It could be worth a go.

    Thanks for your suggestions.



    • Edited by SimeonD Wednesday, March 23, 2016 3:02 PM Spelling
    Wednesday, March 23, 2016 2:32 PM
  • >>In Access 2010, just pick ADP from the dropdown list when you are entering the name for your new database. Or do you mean something different?

    Well, I'll be! I didn't know that choice existed. I do know that 2013 and forward does not have any ADP capabilities and it has been strongly suggested by MS to convert to the ACCDB format (no matter how painful that may be).

    >>He suggests prohibiting select on the Sysreferences table. I can see the Sys.Sysreferences view, but I don't know how to get at the underlying table to change permissions. Any idea on how I would do this? It could be worth a go.

    I would be very wary of doing what that person suggested. he says:

    This does not appear to have created any additional issues
    at this time.

    "does not appear" is pretty lame. Solid testing would set better with me than "It kinda looks okay".

    I think a better approach would be to open a public one-record recordset when the startup form opens and hold that open until the application closes. That way, you have already gone through the iteration of foreign keys.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Wednesday, March 23, 2016 5:46 PM
  • Yep, Access 2010 is the last version that supports ADPs.

    With regards to Sysreferences, it would need plenty of testing on a development version of the database. But still, it should be easy to reverse if required.

    Per your suggestion, I've added the code below to the Switchboard form open. Is this what you mean?

    It hasn't sped up the reports though. Within Access, running the report a second time doesn't speed it up. Nor does a third time. Thinking on this - if the foreign key iteration is the problem, then its occurring every time the report is run. So opening a recordset maybe won't solve the problem?

    Dim rsLink As New ADODB.Recordset
    
    Private Sub Form_Open(Cancel As Integer)
    
    On Error GoTo Err_Form_Open
        
        Dim stSQL As String
        stSQL = "SELECT TestID FROM aaaLink_Table WHERE TestID = 1"
        rsLink.Open stSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    

    Thursday, March 24, 2016 4:42 PM
  • Hi Bill

    I was wondering if you had any more thoughts on this, I'm still stuck.

    Thanks

    Thursday, March 31, 2016 11:08 AM
  • Simeon

    I've tested all my applications for report opening times both as a sysadmin and a regular user. I am not seeing a difference like you do. All I can think of is for you to run some maintenance on the database such as shrinking, re-indexing, etc.

    Sorry I can't be of much help to you.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Thursday, March 31, 2016 6:17 PM
  • Bill

    Don't worry, thanks for all your help. At least we've ruled out a few more items, and I now know what the actual cause is.

    I'll update here on the happy day I find the solution!

    Friday, April 1, 2016 2:52 PM