Results 1 to 15 of 15
  1. #1
    Odeh Naber is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Location
    Cascais, Portugal
    Posts
    11

    Splitting Databases

    Good day!

    This past weekend I was reading up on normalization of tables, recommended to me by Colin (isladogs) in my first thread here on the forums.
    It was very insightful (thank you for that, Colin!), and in one of the articles that I read, splitting databases was mentioned, into front-end and back-end, for the following purposes:

    1. Allowing several users to share over a network.
    2. Performance improvement.
    3. File corruption prevention.

    I found this very interesting, and reading further on this topic, I understood the following general rules:

    a. all tables should all be on the back-end files.
    b. all forms and reports on the front-end files.

    But I read conflicting information as to where the queries should be stored > some sources state they should be on the back-end, while others state they should be on the front-end.

    May I ask forum users, who use split databases, what your insights / experiences / suggestions are on this topic? Some first questions that come to mind are:

    i. How are the files/folders organized?
    ii. Is it possible, recommended or not recommended to have multiple back-end files for one front-end?


    iii. Should there be only one front-end file? Any cases where I should consider multiple front-end files?
    iv. How are queries stored and structured (in front-end, back-end, or both)? Any cases where I should consider queries being stored anywhere other than the front-end?
    v. I understand that the front-end file will link to tables from the back-end. Any cases where I should consider having back-end files linking to other back-end files?

    Thank you in advance!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    i. I have a folder on network that only I can access that holds the master original of frontend that I use for editing. When I deploy a new version I copy frontend outside that folder so users can access for downloading to their local drive. The backend is in another folder.

    ii. This is certainly possible, and may be necessary if backend grows large enough to exceed Access 2GB size limit - or migrate to some other platform. Keep in mind all the freebies have size limits.
    Also, you may need to link to tables in a db that someone else controls.

    iii. Each user should run their own copy of frontend, always.

    iv. If backend is Access, queries definitely in frontend. I have one situation where a query only exists for an Excel workbook to pull data from it. Frontend and Excel used by different groups and I simply made the choice not to put it in frontend. If backend is SQLServer or some other platform, I think Access can pull from tables or views.

    v. Not that I am aware of.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Odeh Naber is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Location
    Cascais, Portugal
    Posts
    11
    Thank you for your reply, June7!

    Point iii : I meant only 1 front-end file for the entire project, or could the project be split into several front-ends (i.e. one front end for specific tasks, and another one for other tasks, but all linked to the same set of tables).
    Point iv : Yes, the back-end is also Access. I have no experience in other back-ends, but I am reading up on MySql. Not sure if I should venture into that just yet, though.

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Odeh Naber View Post
    a. all tables should be on the back-end files
    Except tables destined for session-based/user-bound info.
    Quote Originally Posted by Odeh Naber View Post
    But I read conflicting information as to where the queries should be stored > some sources state they should be on the back-end, while others state they should be on the front-end.
    You mean saved queries?
    When the query has source table in FE, then it must be in FE too. Otherwise it depends:
    When you have the query in BE, query results are moved to FE. When connection speed is low, it will affect the working speed of your app;
    When you have the query in FE, then every time you change the query design you have to replace all front-ends with new version. When you have it in BE, then unless you change the query name, you don't need new FE versions. NB! When you have queries in BE, you can link them into FE's in same way like tables.
    i. How are the files/folders organized?
    No limitations there except FE and BE must be in same LAN and BE must be available whenever an user uses DB.
    ii. Is it possible, recommended or not recommended to have multiple back-end files for one front-end?
    It is possible. The only downside is that when editing links with Linked Table Manager [in case you changed BE file(s() location(s)], you can't use 'All' option.
    iii. Should there be only one front-end file? Any cases where I should consider multiple front-end files?

    Every user MUST have his/her own copy of FE. User can have several different FE's (different designs for different tasks)
    v. I understand that the front-end file will link to tables from the back-end. Any cases where I should consider having back-end files linking to other back-end files.
    I think all links in FE must be to original file - otherwise I suspect data will be not updated. Tables in BE not linked into current FE can be linked to other files.

  5. #5
    Odeh Naber is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Location
    Cascais, Portugal
    Posts
    11
    Thank you for your reply, Arvi! All points noted.

    Yes, saved queries. So there are advantages and disadvantages, depending where I place the queries. Not sure if my logic makes sense, but I think I am more inclined to save the queries into the front-end. If a front-end file goes corrupt, it would be easier and faster to replace it with a recent backup without putting a halt to the data entry operation.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    @ArvilLaanamets, How do you link Access frontend to queries in Access backend? I have tried. Queries are not presented in the LinkManager.

    iii. Multiple frontends could be a management nightmare if they all link to same backend source. Could use code to manage what forms/buttons users are presented. This requires a "USERS" table and some sort of login process. Fairly common topic.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    As each user will have their own copy of the FE, there is in my opinion no benefit in placing queries in an Access BE.
    Doing so will lead to a reduction in speed when accessing from the FE.
    If the query design is changed, all users will receive that when a new FE is distributed.

    However, in answer to June's question (hopefully), you can specify the source database in the query design using the property sheet but the query itself is in the FE.
    OR taking it one stage further, you can just write something like this
    Code:
    SELECT * FROM ExternalQueryName IN 'ExternalDatabasePath';
    NOTE
    1. You need the single quote around the db path
    2. Make sure the external db is closed. if the external dB is open this can crash your FE.
    3. If you save that, it creates a local query in your FE
    In my opinion, its a technique that is worth using only in specialised circumstances. Not for everyday use

    As already mentioned, you can use linked views (similar to a query) as well as tables with a sql server BE.
    However linked views will often by read only.
    You can also run SS stored procedures from an Access FE

    Many of my FE databases have several BEs (both Access & SS) and many also have linked Excel or CSV 'tables' from which data is imported
    However the links need to be direct rather than through an intermediary database.

    I also have situations where I have several FEs linked to the same BE files for different purposes.
    I've never found it to be a 'maintenance nightmare'.

    Does that cover everything?
    Last edited by isladogs; 11-18-2019 at 11:25 AM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by June7 View Post
    @ArvilLaanamets, How do you link Access frontend to queries in Access backend? I have tried. Queries are not presented in the LinkManager.
    OK. Maybe it is not possible - it was many years ago when I created an Access BE (Checked now - you are right). They all are in SQL Server now.

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Arvil. Did you read my reply?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    Odeh Naber is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Location
    Cascais, Portugal
    Posts
    11
    Thank you for the follow up reply, June7, and for your feedback, isladogs!

    @june7

    - So linking to queries in the backend is not possible. I resorted to build queries on the front-end, using tables linked from the back-end.
    - I also pondered the multiple front-end scenario, but true, just thinking about it is a nightmare on its own. I do have a users table and login process planned, and through that I can give or limit access to certain features of the 'application'.

    @isladogs
    - Yes true, regarding to speed and front-end distribution, so I decided on front-end queries.
    - I thought linking to tables in the back-end, and using the linked tables in the front-end to build the queries. Is it recommended to source to the original table in the back-end file when I build the queries, rather than use the linked tables?
    - Yes your feedback are covering all my doubts and incertainties! I will mark the thread as Solved in a few days, just to wait if anyone else would like to provide insight, and I am still reading on the topic, so I might have follow up questions.

    Thank you once again!

  11. #11
    Odeh Naber is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Location
    Cascais, Portugal
    Posts
    11
    Just a follow up note, In the front-end, I linked to related tables in the back-end. The tables in the back-end are related in a one-to-many relationship, Referential Integrity Enforced, and Cascade Update and Delete enabled.
    When linked to, in the front-end, and I view the relationship, the cascade options are disabled and 'faded out'. Can I be rest-assured that the cascades are in fact enabled in the relationships? Or does something change in the relationship once the tables are linked to?

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    If you have linked to tables in the BE, you should use those in your queries.

    Also to repeat part of my previous answer in post #7
    You definitely CAN link to queries in the backend of a database. I posted code showing how it can be done
    However it is only of any real use if you don't want to save it or indeed if you haven't linked the BE tables used in that linked query.
    As soon as you save that, a new local query is created
    So as previously stated, the feature is of limited use only.

    Relationships are always set in the BE. You cannot modify them in the FE.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Note that 'link' to queries in backend is not set with Linked Table Manager, but via a query in frontend. I have used this technique with SQL statement in VBA, never bothered with a query object.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    June
    You asked how you could link to queries in the backend saying that you had tried. I explained how it can be done
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I know you offered an explanation. Just further clarifying for some who might not be so aware that this linking cannot be managed with Link Table Manager as it is not created with Import/Export Wizard. This "link by query" will not show in Navigation Pane under Tables with the link arrow.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Splitting databases and editing it afterwards
    By Tazmaniac in forum Access
    Replies: 2
    Last Post: 03-25-2018, 01:21 PM
  2. splitting db
    By slimjen in forum Database Design
    Replies: 1
    Last Post: 10-09-2013, 07:22 AM
  3. splitting two databases
    By tagteam in forum Access
    Replies: 2
    Last Post: 08-28-2013, 04:42 PM
  4. Splitting databases and processing speed
    By KathyL in forum Access
    Replies: 11
    Last Post: 03-20-2011, 04:56 PM
  5. splitting databases
    By combine21 in forum Access
    Replies: 3
    Last Post: 12-02-2010, 11:27 AM

Tags for this Thread

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