Results 1 to 5 of 5
  1. #1
    Gail is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    34

    Access 2016 using SQL Server 2016, Multi-user, query to run Stored Procedure

    I have a program in Access that uses SQL Server.
    I have many reports that are based on an existing pass-through query that executes a Stored Procedure. Just before I open the report, I modify this query with the correct Stored Procedure and parameters. This has all worked well until recently.
    There can be a dozen or more people using this program. Two people were printing different Customer Invoices at the same time. The same Invoice printed for both and a Customer ended up getting the wrong invoice. The query was modified by the second user before the report was printed for the first user.
    I’ve been searching and found other solutions, but they only work for ADP.


    Is there a way to prevent this problem from happening?
    Thanks

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    I.e. SQL database is back-end.

    You have a front-end for every user, and a single back-end shared by all users (a regular FE-BE setup). SQL server can't run several procedures in same DB simultaneously - it either waits until currently running one is finished, and then starts next one, or returns an error. It seems, that when you want to run several instances of same procedure, you can get the result for both users from same instance instead.

    Consider to have a view for your report instead and link it to FE. Report is run based on view with parameters.

  3. #3
    Gail is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    34
    Thank you very much for this explanation.
    I will try a View.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Gail View Post
    I have a program in Access that uses SQL Server.
    I have many reports that are based on an existing pass-through query that executes a Stored Procedure. Just before I open the report, I modify this query with the correct Stored Procedure and parameters. This has all worked well until recently.
    There can be a dozen or more people using this program. <snip>
    I can't tell and am curious.......
    Does each user have a copy of the FE on their personal computer?
    Or are all users using one FE?

  5. #5
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    if you make sure that every user has his own session ID on SQL server (= logs in as a different user) , it is perfectly possible to run the same procedure with different parameters at the same time without them interfering. SQL server is a multi-user environment, to know the max simultaneous users allowed on the server use :
    SELECT @@MAX_CONNECTIONS AS 'Max Connections';

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Stored Procedure in MS SQL Server from Access
    By jaryszek in forum Access
    Replies: 7
    Last Post: 01-17-2018, 05:58 AM
  2. Automating Outlook 2016 from Access 2016
    By jcc285 in forum Programming
    Replies: 10
    Last Post: 09-30-2017, 01:53 PM
  3. Replies: 1
    Last Post: 06-25-2017, 01:37 AM
  4. Replies: 2
    Last Post: 06-01-2016, 08:52 AM
  5. Replies: 3
    Last Post: 05-23-2010, 05:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums