locked
Questions about front end // back end RRS feed

  • Question

  • As a relative newcomer to Access, I've been developing my first full-scale DB, and will soon begin preparing it to go on-line. As I get closer, I'm realizing how little I know about this aspect of development, and the few courses I've taken didn't touch on it at all. I got some information on a different thread from Joy Eakins, and have been researching and reading. A couple of questions have come up on which I've not yet found clear info:

    1. My primary reference text (Access 2010 Inside Out) makes this comment: "One disadvantage to using the Database Splitter wizard is that it splits out all tables that it finds in your original desktop database." In my case (as I'm sure is common) there are a few static tables used for Lookups (such as zip codes) that should reside on the FE rather than the BE. If I use the splitter, will it be difficult to edit afterwards to get these tables where I want them?
    2. Some tables that will be used for Lookups will not be static, and will need to stay on the BE. This includes the table used for the log-in form. Will having these tables on the BE have a serious negative impact on performance? We're talking a sytem with maybe 30 users, probably no more than 2 or 3 on simultaneously (OK. maybe 4 or 5 on a Friday afternoon.)
    3. Joy Eakins's suggestions indicate that a good way to control who has access to what forms is by creating different front ends for different user levels. Similarly, different users will have need of different Lookup tables. Again, if I use the splitter, will it then be difficult to modify the FE files for different users?
    4. Other reading I'm doing suggests that the most stable, efficient, and secure way to distribute my FE files is to first convert them to ACCDE files. Are there any negatives with this approach that I should be aware of?

    Thanks!


    —nick

    Monday, April 30, 2012 4:22 PM

Answers

  • 1) You can either split the database manually (it's not that hard), or import some tables back into the frontend after using the Database Splitter.

    2) 5 simultaneous users should not be a problem at all.

    3) Creating different frontends shouldn't pose great problems, but wait as long as possible. Having to apply (almost) identical design changes to multiple frontends can be a chore, so the less often you'll have to modify the frontends, the better.

    4) .accde databases work well, but since you can't modify the code in an .accde you'll have to distribute a new .accde each time there is a design change. Again, wait until the design is fixed, or as near to it as you can get.


    Regards, Hans Vogelaar

    • Proposed as answer by KCDW Monday, April 30, 2012 4:48 PM
    • Marked as answer by Nick Vittum Monday, April 30, 2012 5:02 PM
    Monday, April 30, 2012 4:32 PM

All replies

  • 1) You can either split the database manually (it's not that hard), or import some tables back into the frontend after using the Database Splitter.

    2) 5 simultaneous users should not be a problem at all.

    3) Creating different frontends shouldn't pose great problems, but wait as long as possible. Having to apply (almost) identical design changes to multiple frontends can be a chore, so the less often you'll have to modify the frontends, the better.

    4) .accde databases work well, but since you can't modify the code in an .accde you'll have to distribute a new .accde each time there is a design change. Again, wait until the design is fixed, or as near to it as you can get.


    Regards, Hans Vogelaar

    • Proposed as answer by KCDW Monday, April 30, 2012 4:48 PM
    • Marked as answer by Nick Vittum Monday, April 30, 2012 5:02 PM
    Monday, April 30, 2012 4:32 PM
  • Thank you. You're not only quick, you're telling me exactly what I wanted to hear. Your answer to #4 reminds me of one question I forgot to ask, though:

    If I make additions or modifications to the "parent" DB in the future, as of course I will be asked to do, but these modifications don't affect other users, will their ACCDEs still work? (Example: the majority of users will be at what I'm calling level 5, where they will have access to only one form, where they enter their service hours. If my Exectuve Director, who will be at level 2, asks me to create some additional reports for her or other admin staff, will this have any impact on the users at level 2?)


    —nick


    Monday, April 30, 2012 4:48 PM
  • I don't see a problem with having all your Tables that are related to data in the BE. All my lookup tables are in the BE. I can't imagine having them in the FE


    Chris Ward

    Monday, April 30, 2012 4:50 PM
  • Obviously, if you make a change for level 2 users, that will affect level 2 users. But it shouldn't affect level 5 users - they can go on using the same ACDDE they already had.

    Regards, Hans Vogelaar

    Monday, April 30, 2012 4:50 PM
  • Changes you make  in your FE will only change the way the FE handles the BE. Only if you change the BE will you have issues where the FE's have trouble.

    In general I always replace all Front Ends that are the same type at the same time. If you are doing this as an internet form in 2010 I think you only have to change it there and not on the machines.


    Chris Ward

    Monday, April 30, 2012 4:54 PM
  • Great!  Thanks, Hans.

    —nick

    Monday, April 30, 2012 5:02 PM
  • Thanks, Chris.

    —nick

    Monday, April 30, 2012 5:04 PM
  • I don't see a problem with having all your Tables that are related to data in the BE. All my lookup tables are in the BE. I can't imagine having them in the FE


    Chris Ward

    Chris, even in the case of a static table (like zip codes) would you still place it on the BE?

    —nick

    Monday, April 30, 2012 5:12 PM
  • Well not familiar with the terminology of a Static Table but if it is just a Table you lookup data in I don't see how it could function any other way.  Oh this thread?

    http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/fd12f750-e060-4da3-a66a-17f8ece57b06

    If this is what you are refering to then yes this table belongs in the BE.


    Chris Ward

    Monday, April 30, 2012 6:18 PM
  • Well, I don't know if "static table" is official terminolgy or not... I encountered it somewhere and now I don't know where, but I took it to mean a table that contains information that's unlikely to be changed or added to in the life of the database.

    It was the book I referenced above (which refers to them as "local tables") that recommended placing tables like these on the front end to avoid overloading bandwidth between FE and BE. But maybe they were talking about databases with a lot more users than I have to worry about.


    —nick

    Monday, April 30, 2012 6:31 PM
  • Maybe. I would think that since the Post Office adds new Zip Codes every year, and since people and companies can move that it would not be a static Table then. Also this is such a minute amount of data going accross a network that it would not have a bearing.

    Chris Ward

    Monday, April 30, 2012 7:38 PM
  • Nick.Vittum wrote:

    3. Joy Eakins's suggestions indicate that a good way to control who has access to what forms is by creating different front ends for different user levels. Similarly, different users will have need of different Lookup tables. Again, if I use the splitter, will it then be difficult to modify the FE files for different users?

    Why not just control what is visible on your main menu depending on the
    user level?   Then everyone gets the same ACCDE.

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    Tuesday, May 1, 2012 3:28 AM
  • Nick.Vittum wrote:

    4. Other reading I'm doing suggests that the most stable, efficient, and secure way to distribute my FE files is to first convert them to ACCDE files. Are there any negatives with this approach that I should be aware of?

    Just to clarify this.  Once you've done the splitting then yes.  Make an
    ACCDE and distribute that to the users.   While this may be obvious to
    many note that you MUST keep the ACCDB around so you can make changes
    to it in the future.  Then make a new ACCDE from it and distribute the
    new ACCDE.

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    Tuesday, May 1, 2012 3:30 AM
  • . . . While this may be obvious to many note that you MUST keep the ACCDB around so you can make changes to it in the future. . . .


    Tony Toews, Microsoft Access MVP

    Thank you Tony. Yes, I have that in my mind.

    —nick

    Tuesday, May 1, 2012 12:32 PM
  • Why not just control what is visible on your main menu depending on the user level?   Then everyone gets the same ACCDE.


    Tony Toews, Microsoft Access MVP

    Thanks Tony. This was the direction I was going when I recieved Joy's comment about using different font ends for different users. So, in your opinion, this would be simpler or cleaner method that using multiple font ends?

    —nick


    Tuesday, May 1, 2012 12:36 PM