Results 1 to 14 of 14
  1. #1
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65

    Splitting Database - Does Backend need to be only Tables

    Hi,
    I inherited 6 databases that do the same thing for different business units. They are littered with ad hoc queries and make tables over the past 10 years and am looking to start clean. Since they all do the same function my plan is to consolidate to one database, front/back end. The frontend users will basically add projects to a table with all detail (amount, funding project, vendor, etc….) based on their business unit. Other than this form they may have a couple of queries to look things up but their functions are pretty limited. My function is to run actual results against these projects which requires a lot of queries, make tables, macros, etc… that are run weekly.

    My thought is I start with a blank database and build only the tables, forms and queries related to the front end, split it, then continue to use the backend for my maintenance queries, macros, etc… But…. Everything I’ve read about splitting a database says only the tables remain in the backend. Should I just be creating another front end for my maintenance as well? Any guidance to help me design right out of the gate would be really appreciated!!


    Thanks!
    Eric

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Think about it this way: if you put tables in the fe they would likely contain records, even if they were supposed to be only for that user. When you modify and release a new fe, what happens to the records in a user's old fe? So what tables are "related to the front end", what are they for and why can't the go into the be? And what "maintenance" are you referring to? Editing/adding records? That's not maintenance.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65
    When I say tables related to the frontend, they would stay in the backend. Other than the tables they are creating records for, there are vendor, cost center and state tables that are the row sources for lists on the form they use. Probably maintenance is a poor word choice. This is one part of my process: when financial actuals come in there is matching against the projects that the frontend creates, if the project number was coded incorrectly in a subsystem, I have macros that create error lists to correct. The corrections are made in the backend. We have three subsystems that are each handled differently so there are macros for each. This part of the process is not applicable to the frontend of adding projects. I guess if I could run my processes from a front end as well I could split at the end, there will just be a lot of queries in the frontend that will not be relevant to the frontend user.

  4. #4
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65
    Here are 3 scenarios I am looking at to hopefully clarify....

    Click image for larger version. 

Name:	Database Design.jpg 
Views:	29 
Size:	201.0 KB 
ID:	43703

  5. #5
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi
    All tables should be in the BE

    All Queries, Forms, Reports, Macros and Modules should be in the FE.

    You would then have a Main Menu Form (Switchboard) to allow users to Navigate to specific areas of the Database.

    You can use Passwords on Command Buttons for sensitive areas.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  6. #6
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65
    Thanks Mike! Do make tables work on linked tables? Just trying to think if I need to create a table for future reporting needs that won't be used by any FE users except myself.

    Eric

  7. #7
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Eric

    I would stick with creating Reports as required and only allow access to Reports depending on need.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  8. #8
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65
    Thanks Mike. Another poor choice of wording on my part. I'll be using Tableau for reporting and dashboards. It's more for my background work of creating queries and make tables for matching actual invoices to projects and exception queries. After I'm done building and splitting there will likely be new needs that come up that will require me to add queries and make tables. Does the linked table have any limitations vs. having the table within the database?

  9. #9
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Are you asking about temporary tables? If so I'd put them in the front end or even a third db file.

    https://www.experts-exchange.com/articles/9753/Creating-and-using-Temporary-Tables-in-Microsoft-Access.html

  10. #10
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Not that I am aware of.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  11. #11
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65
    Hi Kd, No I was splitting a database FE/BE all users but me have simple function of adding records to a form. I have a lot of extra database work with queries and macros to make the whole process work. It sounds like I should keep the BE to tables only. My question was what if I need to add a table (or make table query) for my purposes after splitting? I guess I could create the table in the BE then link to my FE only.

  12. #12
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65
    Thanks Mike. I'll create a small sample database and play around.

  13. #13
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    My question was what if I need to add a table (or make table query) for my purposes after splitting?
    If that means a table per project for example (as per your graphic) then that is the wrong approach. One table for all projects - same as for any other type of entity. Anything that needs to be specific to a user or level of users needs a field(s) to accommodate that requirement - perhaps userID.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65
    Thanks Micron. Yes, definitely one table for all projects. Business Unit creates a project with form and updates a table with business unit and unique project number assigned.

    Eric

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

Similar Threads

  1. Splitting Tables
    By roxdrob in forum Database Design
    Replies: 16
    Last Post: 10-09-2017, 10:13 PM
  2. Replies: 11
    Last Post: 09-07-2017, 09:20 AM
  3. Replies: 8
    Last Post: 10-27-2016, 06:24 AM
  4. Splitting database: "There are no tables"
    By WithoutPause in forum Access
    Replies: 3
    Last Post: 04-27-2015, 10:46 AM
  5. Splitting tables
    By MTSPEER in forum Access
    Replies: 7
    Last Post: 06-04-2013, 06:47 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