none
Problem with multi-user capability with Access back-end database RRS feed

  • Question

  • I have an Access 2016 application that I have split into back- and front-ends.

    I have the back end on a shared network folder.

    I've two copies of the front-end, distributed to two users.

    Both users of the Front-End have full create/edit/delete permissions for the folder that the back-end is in.

    The front end consists of forms and reports...users do not open linked tables directly.

    The problem: only one user at a time can get on. Once one user is on, other user gets message saying "Could not use <Back-endFilename>; File already in use (Error 3045).

    The system works fine for the one user who does get on.

    Why will it not allow more than one user to access the back-end through the font-end?

    Tuesday, June 28, 2016 5:21 PM

Answers

  • Hmm, let's confirm...

    Create/Edit/Delete is not FULL permissions.  Make sure they have FULL permissions which should include Modify and Read and Execute.


    Gina Whipp
    Microsoft MVP Access 2010-2015
    Access Tips: www.access-diva.com/tips.html
    Blog: http://regina-whipp.com/blog/


    • Edited by Gina Whipp Tuesday, June 28, 2016 11:52 PM
    • Marked as answer by Philjamaica Wednesday, June 29, 2016 1:17 PM
    Tuesday, June 28, 2016 11:45 PM

All replies

  • Unless something has changed in the past couple of years, it is not supposed to work. Access is not designed to be used by multiple users. I really doubt that Microsoft will change that; they want you to purchase SQL Server for multi-user purposes. I am not familiar with licensing issues, but a SQL Server Standard license might cost about $800 but then you will have all the resources of it. Alternatively, Azure might be a possibility.


    Sam Hobbs
    SimpleSamples.Info

    Tuesday, June 28, 2016 8:07 PM
  • What are you talking about?!

    Access has been, and always will be a multi-user database, but proper setup is required which simply involves splitting the database into a Back-End (tables) and Front-End (queries, forms, reports, VBA, ...).  You place the BE on a centrally accessible locations (typically a server), relink the tables in the FE to the new location of the BE and then give each user their own personal copy of the FE to use.

    Based on the symptoms described above, I wonder if the Default open mode for your database has been set to Exclusive?  You can validate this on each user's PC:  File -> Options -> Client Settings -> Advanced -> Default open mode

    Also, what permissions do your users have on the directory in which the BE has been placed?  They need Full Permissions.


    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Tuesday, June 28, 2016 9:42 PM
  • No need to be sarcastic.

    Look at Microsoft Access Multi-User Applications | Database Solutions for Microsoft Access | databasedev.co.uk. It says "Since its creation, Microsoft Access has always been a single user database application". I believe you that that has changed and I said "Unless something has changed". I know that years ago Microsoft was saying that Access is not multiuser. Okay, so that has changed; sorry about that.



    Sam Hobbs
    SimpleSamples.Info

    Tuesday, June 28, 2016 10:04 PM
  • Access has never been solely a single user application, I don't care what source is quoted.  It has been used by proper developers as a multi-user application for decades.

    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Tuesday, June 28, 2016 10:07 PM
  • I forgot to inquire as to how your users are launching the database?  double-clicking on the file itself, batch file, vbscript, other... ?

    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Tuesday, June 28, 2016 10:08 PM
  • Re the default open mode, both users have this set to Shared.

    Both users have Full Permissions on the back-end folder.

    The front-end app is started by double clicking on the file. The app consists of a bunch of forms and reports, with a fair bit of vba, but it doesn't have a splash screen form. You just open the file and then click on the form you need.

    I've read that the exclusive lock happens whenever you open something in design mode, or open a table directly. These are definitely possible with this front end, but I've had both laptops in my office when troubleshooting, and ensured that nothing was open in design view, and problem still persists.

    Tuesday, June 28, 2016 10:18 PM
  • Hmm, let's confirm...

    Create/Edit/Delete is not FULL permissions.  Make sure they have FULL permissions which should include Modify and Read and Execute.


    Gina Whipp
    Microsoft MVP Access 2010-2015
    Access Tips: www.access-diva.com/tips.html
    Blog: http://regina-whipp.com/blog/


    • Edited by Gina Whipp Tuesday, June 28, 2016 11:52 PM
    • Marked as answer by Philjamaica Wednesday, June 29, 2016 1:17 PM
    Tuesday, June 28, 2016 11:45 PM
  • @Sam:

    Not sure where Leidago got the erroneous information, but if you look at the Access Specification published by Microsoft, you will see that the number of concurrent users is listed ass 255.

    Access 2007  Access 2010 

    No significant changes were made in 2013 or 2016 to the desktop specifications. I didn't find 2003 or earlier, but multi-users is not a new feature in Access.

    In a practical sense, 255 is overstating the capability of a data entry solution, it may be accurate for a reporting solution.  Many Access solutions, with ACE backend, accommodate 50-70 without any issues.  Design and network limitation will impact one's mileage from that.  Having 2 concurrent users is not the problem here.


    - Doug

    Tuesday, June 28, 2016 11:58 PM
  • @Phil:

    Try placing 2 copies of the FE in the same folder as the BE and see if you can open them at the same time from 2 machines.


    - Doug

    Tuesday, June 28, 2016 11:58 PM
  • Daniel Pineault said "What are you talking about?!" as if I am an idiot and said "I don't care what source is quoted.".

    And then you are stoking the flames. I have already said that Access can be used for multiple users, why do you have to insist that I am wrong?

    This is the type of thing that often happens in other forums but it should not be happening here.

    Can you please just accept the possibility that Access might have been improved compared to the early days?



    Sam Hobbs
    SimpleSamples.Info

    Wednesday, June 29, 2016 12:39 AM
  • @Phil

    When the first user opens the front-end and opens a form bound to a linked table, is a lock file (.ldb or .laccdb) created in the back-end folder?  It should be if shared access is to be successful.

    If not, try having that user create a file in that folder - just right-click > New > Text Document is sufficient to test it.  If that fails then it is a permissions problem.

    If the user CAN create a test file, then the problem probably lies in the code of the front-end.  Opening a linked table will always open the back-end for shared access if possible, no matter if the front-end is opened exclusively or not.  Is it possible that the front-end has some code that opens the back-end by some other means - for example the OpenDatabase method, or perhaps an ADODB Connection?


    Graham Mandeno [Access MVP]

    Wednesday, June 29, 2016 12:47 AM
  • Hi Sam

    Sorry, but the article in the link you posted is VERY wrong.  I have been using multi-user Access databases very successfully since Access 2.0 on Windows 3.1, and I have never known any corruption to have occurred because of concurrent access.  The most usual cause of corruption has always been a glitch in a network connection.

    The other "problem" that is cited is "A poorly coded and designed application will generally run slower as it processes more data and have more users accessing it".  Well, really??  I think this is a problem which applies to all applications, not just MS Access :)

    Best regards,
    Graham


    Graham Mandeno [Access MVP]

    Wednesday, June 29, 2016 12:57 AM
  • @Sam,

    Kind of hard to accept as when I first started using Access (version 2.0) it was to modify an accounting system (Traverse by OSAS) which was most definitely a multi-user system.  So the premise that it has improved to handle multi users is incorrect.  The only improvement would be in features... just saying.


    Gina Whipp<br/> Microsoft MVP 2010-2015 (Access)<br/> Access Tips: www.access-diva.com/tips.html<br/>

    Wednesday, June 29, 2016 1:06 AM
  • I did not intended to stoke any flame. I wanted to clear up misinformation that you happened to post as a fact:

    "Unless something has changed in the past couple of years, it is not supposed to work. Access is not designed to be used by multiple users. I really doubt that Microsoft will change that; they want you to purchase SQL Server for multi-user purposes."

    I do accept the possibility that Access have been improving.  As it is one of my go-to tools, I hope it will keep on improving in the years to come. As you seem to be in the same mindset, maybe researching before posting information that is at best outdated, at worst, totally wrong, based on a single source is a prudent step.


    - Doug

    Wednesday, June 29, 2016 1:32 AM
  • .

    Can you please just accept the possibility that Access might have been improved compared to the early days?



    Sam Hobbs
    SimpleSamples.Info

    Sam,

    You can't believe everything you read on the Internet. I don't know what your experience with Access is, but that article was very wrong. I've written to the webmaster of that site to have that article removed. I know people who were involved with the initial development of Access. Access was designed from the very beginning to be multi-user. Nothing has changed in that regard. It is not that Access was improved. The fact is that you read an incorrect article, believed it and spread its misinformation. I'm sorry if that sounds harsh and I don't believe you did it deliberately, but the facts are clear.

    Scott<>

    Access MVP since 2007
    Author: Microsoft Office Access 2007 VBA (Que Publishing)



    Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2009 Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

    Wednesday, June 29, 2016 2:00 AM
  • Reply to the person asking the question, not me. The next person that continues this, I will report.

    I disagree, but this is not the place for that. It is unfair to continue to bring this up; I am trying to do the right thing and not continue on an off-topic path.



    Sam Hobbs
    SimpleSamples.Info

    Wednesday, June 29, 2016 2:31 AM
  • You can't believe everything you read on the Internet.

    I trust my memory, but this is not the place to argue that. Stop trying to argue.


    Sam Hobbs
    SimpleSamples.Info

    Wednesday, June 29, 2016 2:38 AM
  • You can't believe everything you read on the Internet.

    I trust my memory, but this is not the place to argue that. Stop trying to argue.


    Sam Hobbs
    SimpleSamples.Info

    This is the problem. You have several experienced Access developers, many of them MVPs who are telling you what the fact is. That Access was designed from the very beginning to be multi-user. We are not trying to argue, we are trying to correct misinformation. You are the one arguing by continuing to dispute the facts. We are just trying to make sure that accurate information is being posted.

    Scott<>



    Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2009 Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries


    Wednesday, June 29, 2016 10:21 AM
  • Thanks Gina, that fixed the problem.

    Wednesday, June 29, 2016 1:19 PM
  • @Sam Hobbs.

    I quote from your first post:

    "Unless something has changed in the past couple of years, it is not supposed to work". Your intention was to imply that any multiple user capability was added "in the past COUPLE of years", if at all.

    You made an obviously untrue claim there, but then began backing away from it as soon as a group of knowledgeable people challenged it's veracity.

    But let's give you the benefit of the doubt and accept the possibility that, the last time you personally used Access, twenty years ago in the mid '90s, it was not designed for multiple users. Since then, it looks like you've not bothered to update your knowledge with personal experience. Instead you relied on a questionable comment in a third-party site that itself hasn't been updated in at least 10 years (those screenshots are from the 2000 or possibly 2003 version of Access, making it at least that old, if not older).

    So, given that set of circumstances, it's hard, very hard, to take your protests very seriously. Being proven wrong is not a pleasant experience, of course. And no one takes pleasure in doing that to you. You have only yourself to hold accountable, though.

    I see by your profile you are a prolific poster here. Good for you. May I suggest, though, that in future you stick to those areas in which you do have some knowledge? We'll all be better for it. Especially the new developers who come here looking for reliable feedback from reliable sources.


    With Joy Wend Your Way

    Wednesday, June 29, 2016 2:13 PM