Results 1 to 7 of 7
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Frontend/Backend question (Access 2000)

    Hello Nannette,

    In a frontend/backend setup, the backend database contains only the tables (and their relationships) and nothing else whatsoever. The frontend databases (which may reside on a file server disk or on a local disk) contain everything else: queries, forms, reports, macros, modules, custom toolbars.

    A frontend/backend setup in Access is not a client/server solution - Access is not a database server. The backend database is a passive data container, all data processing is handled by the frontend database.

    You can set startup options in the frontend database, and you can prevent the user from bypassing them using the method from the link you provide, but for complete security, you will have to set user-level security on both the frontend and backend database. Otherwise, people will still be able to see and edit data in the backend database by linking to the tables from another database.

    See ACC: Microsoft Access Security FAQ Available in Download Center for a good overview of user-level security by Microsoft. Moderator <!profile=WendellB>WendellB<!/profile> has an excellent tutorial on his website (you'll find a link in his profile).

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Frontend/Backend question (Access 2000)

    I am a bit confused on the frontend/backend theory. I hope someone can help me ASAP on this confusion. Firstly, when the tables are on the backend, and workstations access those tables on the server, does Admin have control over the toolbars, shortcut menus, MasterForm and bypass key options because the tables exist on the server? Is there a need for a Masterform, or (Switchboard), when the user only has linked access to a table anyway? I am beginning to understand the logistics of the following:

    File-server database solutions :
    The simplest form of file-server solution is implemented by copying an Access database (.mdb) up to a network share so that it can be opened and shared by multiple users. A more sophisticated approach is to create a "front-end/back-end solution" that uses a front-end Access database (.mdb) running on each user's computer that contains linked tables that connect to a back-end Access database (.mdb) on a network file server. A front-end/back-end solution is similar to a client/server solution in that it has both a client (front-end) component and a server (back-end) component, but it is important to note that the back-end component is simply an .mdb file residing on a network file server, and that the database engine that is managing communication with the back end actually consists of multiple copies of the Jet database engine, each of which is running from a client workstation.


    I understand the concept of the following exercise given by Grahmns web site:

    http://www.gj.thorpe.btinternet.co.uk/shiftbypass.html

    And it works absolutely beautiful, however...
    Am I wrong in thinking this solution is for a workstation that may have several users sharing a database that is on "that workstation"...and not necessarily on a server somewhere? Can this be created in the database on the server side, and the workstations will then not be able to bypass the startup options? OR....Are they not able to bypass the startup options anyway, because the tables actually reside on the Server, and they are just linked to the tables? Therefore, there would be no need to worry about intrusion, whether it was intentional or misguided into the design of the database on the server. The users are simply linked to whatever tables they are allowed to link too, via the Admin person on the server side setting up workgroups, rights and to who uses the tables and how etc....

    Thanks for the feedback...
    Nannette
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Frontend/Backend question (Access 2000)

    Hans,
    Wendalls site is where I actually have been the last few hours. I have gone over and printed his tutorials (lots of great info). I am still just a tad confused (no mega confused)...and wonder if you could tell me a bit more, along the same question:
    Scenario:
    I have 25 users at different workstations. I need to have a database backend that holds the tables and their relationships...what is the best way (I have read so many methods today, I'm gaga)...to set this up? Is it done on the backend? Do I load the database tables there as an mdb. file, and then allow the 25 users to link to that path? Using what? The linked table manager? When they back up their data everyday does it automatically update the mdb. file on the backend? Your saying that the backend holds the tables, and is a container...but is not a server. Confused by that a bit, but will work on it...it is so confusing to me.
    The table I will put on the backend is just a table, so it is nothing more then a container holding data that my users link to which updates any thing they have done in the course of a day as they work, correct? How in the world can they bypass any startup options if they are only linked to the backend table. They would be creating their own forms, reports etc...on their own workstations, using their own toolbars, shortcut menus and the like....so what would the security be for? I mean if they are only linked?
    Confused pandomonium here...are you sighing yet. [img]/forums/images/smilies/smile.gif[/img]
    Nannette
    PS...am i misreading this info: taken from
    http://support.microsoft.com/default.aspx?...p#_Toc493299674

    Regardless of the setting in the link, a user cannot inherit higher permissions than those set on the base table. In other words, granting full permissions on an attached or linked table in the front-end, including Administer, will have absolutely no effect on the actual table in the back-end- if you have removed all permissions in the back-end, users will not be able to open the table in either Design or Datasheet view in the front-end where you have granted full permissions. The important thing is to restrict permissions as necessary in the back-end database (where the tables actually reside). This will prevent users from opening the back-end tables directly.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Frontend/Backend question (Access 2000)

    Nannette,

    If you already have a database that contains both tables and queries, forms, reports etc., you can use the Splitter Wizard (Tools/Database Utilities/Database Splitter) to create a backend and frontend.

    If you still have to create everything, first create a database with the tables; set the relationships in this database (Tools/Relationships). Then move this database (the backend database) to a folder on a network disk and make sure that all intended users have read/write access to this folder.
    Next, create a new database and use File/Get External Data/Link Tables... to create links to all tables in the backend database (the Link Tables dialog contains a useful "Select All" button). In most situations, the developer would create a complete application with queries, forms, reports etc, in this database (the frontend), and then make (copies of) it available to the users. In such a frontend database the developer would set startup options to make sure that the users only use it the way intended by him/her. If you're going to let your users create their own forms etc., I wish you strength.

    If you trust your users, and if your network is secure, you can get by with limited security. But if you want to make sure that they can't do things they aren't intended to do, you need user-level security. The MS Security FAQ explains how you can remove users' rights on tables and still let them work with the data through queries. It would take too much time and space to explain that here.

    PS You use the Linked Table Manager to modify the links if the path to the backend database has changed.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Frontend/Backend question (Access 2000)

    Hans,
    Okay...let me see if I got this right. The ideal database would be created by the developer. All Forms (including Main Switchboard), and Tables with Relationships would be in this database on the backend. Then, using the Database Splitter I would send the database to a shared network folder, where the Developer would create Security on this database by creating Workgroups with certain rights to the data.
    Then on each workstation, I would use the File...Get External Data...Link command to set up a link to that Database I put on the backend. If I have a main switchboard on the backend, and have made it the startup option, then they will have access to that form first and only (because I will have disabled all other startup options), thus allowing them to access only the elements of the database on the backend that I have allowed them links to as their workgroup permissions dictate, via command buttons on the Master form.
    As the developer, when they open these elements from the Main Switchboard I would like only the toolbars and menu bars that I have created to appear. I am aware of how this is done, but will it actually happen on their machine? They have a stand alone Access application on their system, won't they still have their own toolbars etc...or will they only see the ones I've allowed, because they are linking to my database on the network folder? Is there a need to disable the (hold Shift while clicking on Access Icon) bypass key option still? If I set those options up in my database on the network folder, then they will only have access to the toolbars, menus etc...that I have created corrected?
    I do not want to allow users to create their own forms, just queries, reports etc...from the data they are linked to. They will be allowed that. The only thing I don't want them to do, is access the tables directly, or change the design of the backend database. They will be given access to the forms on the backend, to find, filter, delete, add records depending on their workgroup permissions I've given them.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Frontend/Backend question (Access 2000)

    If you create a database with tables and other objects first, and then use the Database Splitter, you end up with two databases:
    <UL><LI>A backend database with only tables and absolutely nothing else - no queries, no forms, no reports, no macros, no modules, no data access pages.
    <LI>A frontend database that contains all queries, forms etc. from the original database, and links to all tables in the backend database. Therefore, there is no need to use File/Get External Data/Link Tables... - it has already been done for you by the Database Splitter. You only need File/Get External Data/Link Tables... if you create the frontend manually, without the Database Splitter.[/list]You should give your users a copy of the frontend database; they will use that to view and edit the data in the backend.

    If you set user-level security, the security information for the database objects is stored in the database; the security information about users and groups is stored in a workgroup administration file (*.mdw). The users should open the frontend together with the secured .mdw. One of the items in the Security FAQ explains this; you can also do a search for .mdw in this forum.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Frontend/Backend question (Access 2000)

    Hans,
    Thank you for all your valuable time. I appreciate all the feedback on this. You have helped a lot.
    Nannette
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •