none
Question on ACCDE and the laccdb RRS feed

  • Question

  • Fellow Accessors,

    I have a question about ACCDE and its transaction log (laccdb). Please follow closely.

    Access 2010

    I have an access solution for multi users. The DB is split.

    Backend DB with all tables and queries. 

    FrontEnd DB with NO TABLES and NO QUERIES - just Forms

    The gui connects thru VBA and does it works with the backend. Clean.

    Now, you make a ACCDE of the frontend.

    The gui/db is only used by small number of users. We send the ACCDE across to the users and let them drop it on their laptop and it communicates to the backend DB which is on a share. All works.

    The question came up is if we keep the ACCDE on a share and let users click on it from there instead of sending it to them. I know the concept works because it works like an exe - the object is running on their local memory BUT there is still a laccdb open on the share for multi users launching that accde. 

    My question is how access handles the shared translog (laccdb) when an ACCDE is run by multi users from a share - not physically copied to their laptop?  Can their still be contention on the GUI/ACCDE laccdb?  Remember, the gui is not doing anything with any tables inside itself.

    Thanks, MG

    Tuesday, November 22, 2016 5:44 PM

Answers

  • Yes, you want to install + run the software on each workstation.

    Keep in mind you never really had to “necessary” split the database and deploy the software to each workstation. However, just like word, or outlook or ANY OTHER application, you typically install it to each workstation. So there no reason to “single” out Access and NOT install the application on each workstation. As you pointed out you ARE following this practice.

    As for “why” or some “other” reasons. There are quite a few, but the simple reason is that if something breaks, or is corrupted or damaged, then EVERYONE is affected. I mean, if something were to go wrong with a single shared copy of Word, then everyone would have to stop and go home while the ONE copy of word is fixed. So it is really a “redundancy” issue.

    Even with a split database, you will find that an ldb (locking file) is created for the local front end, and then an ldb locking file is created on the server for the back end. The locking file is used for record collisions. It also used to allow users to modify different forms at the same time (but this ability disappeared long ago (access 97 was the last version that allowed this).

    Now as YOU CLEARLY pointed out, what about the accDE that has ZERO tables? In that case the locking file is created but would not be used. So it not big deal.

    At the end of the day, the issue is that with multiple users in that ACCDE then it “can” work, but you are increasing risk that some form setting etc. interferes with other users. For example, if in VBA code you set a forms filter, if you look at the forms filter property, you will find the filter HAS been set to what the LAST user’s VBA code has set. When another user launches that form, they will see that filter setting. However, in 99% of cases this is NOT a problem since when the form loads, the filter is ignored. However, if that VBA code were to execute a filter on without setting the filter, you ARE going to get the filter from another user. So the issues that crop up are NOT related the ldb locking file, but in fact that it is COMMON for VBA code to set things in code like filters or even assign a form some SQL as its record source.

    There is VERY little reason to take this kind of risk and you simply opening up the door that actions of one user will effect another by sharing the front end.

    And if a user’s machine hangs or locks up or some such, they may well leave the front end in some kind of locked state and other users can't use the application. This again effects other users.  And with multiple users then you cannot issue a NEW update to users. I mean why be prevented from rolling out a quick bug fix because one user left home early and left the application open. Now everyone else at the company has to suffer? How does that make any sense? With each user having their own front end, then you can quickly roll out a update and fix that report they need. All users can now exit - re-enter (and your update system engages), and they now have the fixed report for the day, and you don't care about one silly user that forgot to logoff for the day.

    So you “always” been able to run an access application un-split and you “always” been able to have multiple users in the same front end – it just a really bad idea and there are risks.

    I mean having a rock solid and reliable setup is a GOOD thing since EVEN when you have such a good setup, something can go wrong. So support calls and something going wrong can and will occur. But why INCREASE this risk when you can do something about it? You don't have to change the oil in your car, but eventually it will cost more then the time you save by not changing the oil - same goes for splitting.

    If you don’t split (or in your “question” allow multiple user into that front end), then you simply increasing the chance of one user’s problem effecting others users. Since you DO HAVE control over this issue, then the simple answer is why not adopt a setup which increases and ensures that user’s actions and problems are isolated from other users.

    Remember, you not just placing a file on some computer, but placing software. We install word, outlook etc. on EACH work station for this reason of reliability. You can install word on each workstation, and THEN allow word to consume data (a word file) from the server. And in the case of Access, if you talking about JUST data, then again place that on the server. However, if your IT department cannot distinguish between software such as code and forms and a UI and that of a data file, then it likely that IT department needs to be sent someplace to be educated about the difference of software and code and that of a data file that software CONSUMES.

    Access can create JUST a data file, but ALSO the Access development system allows you to create software appclations – and since everyone for 20+ years has installed such software on each workstation, then it makes perfect sense to continue this time honored tradition when creating software with Access.

    Just because you purchase and install commercial software on each workstation does not out of the blue suggest that NOW because you are developing and creating software you break this rule of installing software on each workstation.

    So one requires the ability to understand the difference between data and that of software (and if that concept cannot be grasped - then we already lost the ability to make a reasoned response here). So the main reason here is to “isolate” actions of each user from the actions of other users.

    So the computer industry LONG ago figured out that the EXTRA effort to install word (or your cool application) on each workstation outweighs the advantages of not have to re-distribute a new version each time (which could be eliminated by haring the one application placed on the server).


    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    • Proposed as answer by Chenchen LiModerator Wednesday, November 30, 2016 3:25 AM
    • Marked as answer by mg30 Wednesday, November 30, 2016 2:25 PM
    Wednesday, November 23, 2016 12:26 AM

All replies

  • > transaction log (laccdb)
    No, this is the lock database. There is no transaction log in Access like there is in SQL Server.

    > Backend DB with all tables and queries
    That is highly unusual, but OK, let's follow along.

    > FrontEnd DB with NO TABLES and NO QUERIES - just Forms
    No linked tables? That's unusual, and may be more time consuming to implement, but OK, let's follow along.

    > My question is how access handles the shared translog (laccdb)
    The shared lock database is handled the same as the lock database on the BE. There is no contention.

    You may have hit on that RARE case where sharing the FE in a shared location on the server is OK. Most concerns circle around sharing objects such as local tables that were not designed to be shared, but you don't have any of those. You still have a slightly higher chance of corruption of the FE, but that's no big deal: if that happens just copy another copy of the FE to that location.

    So, you have my blessing  :-)

    Just be aware you're running Access outside of its normal design parameters. I'm quite sure your scenario has had zero testing, but if it works for you, that's great.


    -Tom. Microsoft Access MVP

    Tuesday, November 22, 2016 6:40 PM
  • Why are the queries in the BE?  Odd.  Usually you want them in the FE since they are used as part of Forms, Reports, Macros and VBA.

    I just want to make sure we have the picture right.  Your FE does not used Linked Tables?

     

    You can place the FE wherever you'd like, including a public server shared folder, but each user must have their own copy.  So you still shouldn't share a common copy on a server share.


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




    Tuesday, November 22, 2016 6:58 PM
  • HI Tom,

    Thanks for the info. The current approach has had a LOT of testing with giving users their own copy and communication with the BE - currently in use. The concept of having them go to the share and executing it from there is something I was just asking about - not being done.

    Thanks,

    MG

    Tuesday, November 22, 2016 7:19 PM
  • HI Daniel,

    thanks for the info.

    NO linked TABLES. NONE. The FE is just a gui - period.

    In our design the queries happen to be on the BE. Everything is on the BE for this approach. We want complete disconnected concept for reasons I am not going to get into.

    So, you are saying that multi users should NOT share a FE from a share. You are saying that they should copy it to their desktop? This is the approach we already use.

    Thanks MG

    Tuesday, November 22, 2016 7:22 PM
  • What I will often do is place the master copy of the FE on a public share and then use a vbscript, or other mechanism, to deploy a copy to the end-user.  Then I create a shortcut to the vbscript.  This way it is a one time thing and after that I need only update the single file on the share and the vbscript takes care of updating the end-user's copy.

    You can make it a dumb copy, simply copy over the existing one every time it is launched (can be taxing on networks though), or you can make it first check the version installed and only perform the copy if a newer version is available.


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


    Tuesday, November 22, 2016 7:29 PM
  • HI Daniel,

    Thanks. Yes we already have something in place like this - we deploy to them.

    My previous question to you is why is it that you have an issue with them clicking on it from the share? Are you concerned about them deleting it.

    Thanks,

    MG

    Tuesday, November 22, 2016 7:34 PM
  • Firstly, there has to be a certain minimum level of responsibility from users! Going and deleting files in many companies will get you fired, or at the very least disciplined.

    Regardless of the above, I usually setup Traverse permissions on the Folder.  So they can use the content of the folder but it doesn't actually show up for them in Windows Explorer minimizing their ability to mess anything up!  All they get is a Link to my vbscript.


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

    Tuesday, November 22, 2016 8:08 PM
  • HI Daniel,

    Yes, I understand and agree. I thought you were concerned for some other reason than that. Our current deployment is a push - not pull. all of this came from my question of multi users click on something from a share.

    So, it sounds like you still recommend a push of the accde but if they select from a share, it is done via a shortcut link to the accde. Correct?

    thanks,

    MG

    Tuesday, November 22, 2016 8:27 PM
  • Yes, you want to install + run the software on each workstation.

    Keep in mind you never really had to “necessary” split the database and deploy the software to each workstation. However, just like word, or outlook or ANY OTHER application, you typically install it to each workstation. So there no reason to “single” out Access and NOT install the application on each workstation. As you pointed out you ARE following this practice.

    As for “why” or some “other” reasons. There are quite a few, but the simple reason is that if something breaks, or is corrupted or damaged, then EVERYONE is affected. I mean, if something were to go wrong with a single shared copy of Word, then everyone would have to stop and go home while the ONE copy of word is fixed. So it is really a “redundancy” issue.

    Even with a split database, you will find that an ldb (locking file) is created for the local front end, and then an ldb locking file is created on the server for the back end. The locking file is used for record collisions. It also used to allow users to modify different forms at the same time (but this ability disappeared long ago (access 97 was the last version that allowed this).

    Now as YOU CLEARLY pointed out, what about the accDE that has ZERO tables? In that case the locking file is created but would not be used. So it not big deal.

    At the end of the day, the issue is that with multiple users in that ACCDE then it “can” work, but you are increasing risk that some form setting etc. interferes with other users. For example, if in VBA code you set a forms filter, if you look at the forms filter property, you will find the filter HAS been set to what the LAST user’s VBA code has set. When another user launches that form, they will see that filter setting. However, in 99% of cases this is NOT a problem since when the form loads, the filter is ignored. However, if that VBA code were to execute a filter on without setting the filter, you ARE going to get the filter from another user. So the issues that crop up are NOT related the ldb locking file, but in fact that it is COMMON for VBA code to set things in code like filters or even assign a form some SQL as its record source.

    There is VERY little reason to take this kind of risk and you simply opening up the door that actions of one user will effect another by sharing the front end.

    And if a user’s machine hangs or locks up or some such, they may well leave the front end in some kind of locked state and other users can't use the application. This again effects other users.  And with multiple users then you cannot issue a NEW update to users. I mean why be prevented from rolling out a quick bug fix because one user left home early and left the application open. Now everyone else at the company has to suffer? How does that make any sense? With each user having their own front end, then you can quickly roll out a update and fix that report they need. All users can now exit - re-enter (and your update system engages), and they now have the fixed report for the day, and you don't care about one silly user that forgot to logoff for the day.

    So you “always” been able to run an access application un-split and you “always” been able to have multiple users in the same front end – it just a really bad idea and there are risks.

    I mean having a rock solid and reliable setup is a GOOD thing since EVEN when you have such a good setup, something can go wrong. So support calls and something going wrong can and will occur. But why INCREASE this risk when you can do something about it? You don't have to change the oil in your car, but eventually it will cost more then the time you save by not changing the oil - same goes for splitting.

    If you don’t split (or in your “question” allow multiple user into that front end), then you simply increasing the chance of one user’s problem effecting others users. Since you DO HAVE control over this issue, then the simple answer is why not adopt a setup which increases and ensures that user’s actions and problems are isolated from other users.

    Remember, you not just placing a file on some computer, but placing software. We install word, outlook etc. on EACH work station for this reason of reliability. You can install word on each workstation, and THEN allow word to consume data (a word file) from the server. And in the case of Access, if you talking about JUST data, then again place that on the server. However, if your IT department cannot distinguish between software such as code and forms and a UI and that of a data file, then it likely that IT department needs to be sent someplace to be educated about the difference of software and code and that of a data file that software CONSUMES.

    Access can create JUST a data file, but ALSO the Access development system allows you to create software appclations – and since everyone for 20+ years has installed such software on each workstation, then it makes perfect sense to continue this time honored tradition when creating software with Access.

    Just because you purchase and install commercial software on each workstation does not out of the blue suggest that NOW because you are developing and creating software you break this rule of installing software on each workstation.

    So one requires the ability to understand the difference between data and that of software (and if that concept cannot be grasped - then we already lost the ability to make a reasoned response here). So the main reason here is to “isolate” actions of each user from the actions of other users.

    So the computer industry LONG ago figured out that the EXTRA effort to install word (or your cool application) on each workstation outweighs the advantages of not have to re-distribute a new version each time (which could be eliminated by haring the one application placed on the server).


    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    • Proposed as answer by Chenchen LiModerator Wednesday, November 30, 2016 3:25 AM
    • Marked as answer by mg30 Wednesday, November 30, 2016 2:25 PM
    Wednesday, November 23, 2016 12:26 AM
  • Hi Albert,

    Thanks for the indepth response. Yes, I am aware of this approach and pattern. Our reason for splitting was valid but I will not go into it hear.  I was using Access when it was 97, 2000, and 2003 but have not used it for a quite a few years. I do follow your pattern. We do want independent objects sent to the users - pushed. You have confirmed my suspicion that sharing this ACCDE from the share is not prudent.

    I will pass this on.

    Thanks again,

    MG

    Wednesday, November 23, 2016 2:07 AM
  • You are most welcome.

    And keep in mind that I know some companies to run Access un-split for years without any problems. So if a company is successful running software that is not split, or they share the front end, then really – not a big deal. I know of companies that run software this way for 10 years without an issue. Who am I to complain?

    On the other hand I seen companies run for one week, having nothing but problems and they all were fixed by splitting and deploying a front end to each user.

    So this is not a “must” do, but time and experience in the industry suggests it is a good idea. As noted there is a trade-off of deployment efforts vs increased issues by having multiple users in the same front end. The fact that some companies have had little issues by not splitting still does not make a “general” case to not split and not allow multiple users into a front end.

    So this is not a 0 or 1 issue (binary) in regards to splitting. There are certainly cases that the benefits are GREATER for not splitting – say 2-3 occasional users launching the application from a shared folder. Because this is a “public” form, then I can only give general advice that applies for “most” people – and entertaining “exceptions” is a challenge that likely would mislead most people.

    So splitting and giving each user a separate front end is not always a must do – but it tends to be a good idea for both the short run, and almost always yields benefits in the long run.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Wednesday, November 23, 2016 2:48 AM
  • Hi Albert,

    Thanks again. Yes I totally understand. And I agree. The choice of split or not split comes to Microsofts favorite two words, "It Depends".  For us, the split was the right thing to do for reasons that I won't get into on the forum. We have used this approach for numerous things and it is holding fine.

    Thanks again

    MG.

    Wednesday, November 23, 2016 2:51 AM
  • So I have tried to follow this thread - and I definitely defer to TvS and AK as a super gurus (that have answered questions I have had in the past) - but I worry that this thread could muddle the understanding of newer users and so really want to point out that if it is multi user (simultaneous) it must be split.

    If you really want to put the front end file at a common server point, rather than on each end user's PC, you still must have a front end file per user.  They should not attempt to share the same front end file.  This is a terminal services type deployment - and I can understand in a larger office environment that this may be easier depending on what management tools you have.  One essentially shifts the processing load over to the server more than on the PC of course.

    Also, queries that sit in the back end file cannot be selected to be the record source of a form or report object in the front end file - and I would hate to see a newer user confused on this point also.  Queries belong in the front end file - which is of course where they go automatically when one first invokes the splitter function after the database has been initially designed.


    Wednesday, November 23, 2016 2:17 PM
  • Well said – thanks for the kind comments.

    The rise and use of Terminal Services (aka remote desktop) certainly can “confuse” this issue. I absolute love Terminal Services as an option for running software – especially Access software (and it is a great solution to companies that have Mac/Apple computers on site to run Access applications).

    And to keep the water clear and free of mud?

    Even in a terminal services deployment in which both files (front end, and back end) are on the ONE server, EVEN in this case, it is still recommended to give each logged on user have and receive their own front end – but that front end will actually reside on the terminal server.

    So all of the issues of “settings” and conflicts that can arise from having multiple users share the same front end exists and applies to terminal server deployments. So while each user should have their own front end - terminal services can confuse this issue since the front end for each user is placed on the server, the suggesting still applies - ie: its good practice to give each user their own front end.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Thursday, November 24, 2016 12:48 AM
  • HI msdnPUblicIdentity.

    Thanks for the info. I do agree on ALL your points. Our solution was a custom one that we had to build. The reasons I will not list here.

    Yes, the queries should be in the FE. But that would assume that you are using linked tables. Correct?

    If, for some reason, you were not using linked tables, then you have to take the longer approach of queries on the BE and calling them thru your vb code/connection to the BE. 

    thanks

    MG

    Thursday, November 24, 2016 3:47 PM
  • Hi,

    If your issue has been resolved, I suggest you mark helpful post as answer.

    Thanks for your understanding.

    Regards,

    Celeste


    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.

    Wednesday, November 30, 2016 3:21 AM
    Moderator