Results 1 to 7 of 7
  1. #1
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73

    Multiple Databases?

    Hi all,



    Being a self taught novice of Access, when would it be appropriate to use multiple databases? Is the use of multiple DB's even good practice?

    As I have been constructing my DB I have come to realized that by just have a single DB might be a little too complicated to manage, so by splitting it and making 2 separate databases then linking them together seems to me to be a logical approach (that said please remember I am only a novice!)

    Any advice/tips will be appreciated greatly.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    a) yes you can split the backend into two or more db's
    b) whether it is a good idea to do so depends on the reason why
    c) whether it is a success depends on how you split them

    so little information provided, little advice we can provide. Can only suggest if you split the backend, keep strongly related tables in the same db - e.g. invoice header, invoice lines

    Also from what you say, does not sound like you have split the db into front end/back end. Even if there is only one user, I strongly recommend you do so, if you don't you a) increase the risk of corrupting the database and b) make recovery from such corruption almost impossible and will need to revert to a backup.

    If you want to split the front ends - e.g. one for one group of users and another for another group this is also OK, but the usual advice is to keep everything in one place (easier to maintain) and handle different views/options via a user login and code.

  3. #3
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    Well here is the scenario I have:

    I work for a company that manufactures trailers. Within this company are several different "departments" for example, sales dept, parts, warranty/service etc.

    Currently the parts dept. is running a stand alone excel system which has several different work books for different types of products we produce, though some parts are used on more than one product. This side of things is quite extensive as there are many parts we buy in and/or manufacture in house for the range of trailers we produce. It is here that I think perhaps the parts department should be a separate database and have any other department that need to reference this data (for example service and warranty) link into it. Only reason I have for going about it this way is the amount of tables I will require for normalizing the data and creating relationships...I fear it will be fairly vast!

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    number of tables should not be an issue (Access can up to 32k of objects - a table is an object, as is a query, form, report or module) - the question is how much/what data is common to all departments, how much/what data is primarily used by one department and occasionally referenced by other departments and how much/what data is unique to one department and not accessed by other departments at all. Also consider the number of users - the more you have the bigger the potential impact on performance, the limit is 255 concurrent users but in reality somewhere between 10 and 50 depending on how efficiently code and queries are written and the efficient use of indexes.

    The complexity will not be in the tables/relationships, but in the navigation of forms and reports.

    From what you describe, unless you are hitting the 2Gb db size limit (in which case consider using SQL Server as a back end instead), I would keep all the tables in one db and perhaps have different front ends for different departments - but be prepared for a fair amount of duplication of code and higher maintenance cost for the common elements. As suggested before, control who sees what from a login profile.

    I recommend you adopt a proper naming convention for tables and fields, forms and reports so objects can be found quickly and logically

  5. #5
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    Well it's only a small company, so no more than 10 users so performance shouldn't be an issue.

    I think that all departments will need to access the data from one another at some point, how often this will be I cannot tell so with that it needs to be there for all to see. The main data will be that of the spare parts department, however this data will be created by production/design when developing new products as it is here that dictates what parts get used, from where and whether or not it is manufactured in house or out sourced from one of our suppliers. At some point I would like to allow the DB to create specification sheets for each product ordered from the sales department as this will denote what parts are required.

    When naming things like tables/forms, I was taught to use the prefixes such as tblABC, frmXYZ and so on but does the same apply for naming reports and queries? Also what about underscores? Will these prove to be more of a hindrance than a help in the naming of objects as I use them a lot?!

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Your ideas on naming are good practice. Also, you should not use embedded spaces or special characters in your field and object names --this will reduce syntax issues.

    Before jumping into Access too quickly, I suggest:

    -you have a clear statement/description of what you are trying to solve/automate
    -validate your description of requirements with others to ensure you have a clear and complete picture
    -identify who is sponsoring/requesting this automation
    -don't underestimate the importance of a team to identify/resolve issues and/or approaches
    -build a data model that encompasses your entire business (if only to identify the extent of the business)
    -identify priorities for development with bosses/management (don't be a lone pioneer!!!)
    -don't underestimate the importance of communications and clarity ( some prototyping to get feedback will help)
    -review database design concepts and normalization
    -some great database design and background videos start here
    -see my stump the model for ideas

    Good luck.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    When naming things like tables/forms, I was taught to use the prefixes such as tblABC, frmXYZ and so on but does the same apply for naming reports and queries?
    Any object - includes reports, queries and modules, variables, controls...

    Personally I avoid underscores as well but only because I find code easier to read without them.

    For tables I use a lowercase 3 character prefix to indicate the purpose of the table and uppercase the first character of each word e.g.


    dtaAddresses (data)
    lupAddressType (lookup)
    dimDates (dimensioning data)
    sysConfig (system data)
    etc

    forms, reports and modules get a similar naming convention based on where they are used - such as import, data management, processing, maintenance, admin, reports etc.

    I don't like lots of typing so tend to keep names short but meaningful.

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

Similar Threads

  1. Switching between multiple databases
    By developer11 in forum Access
    Replies: 2
    Last Post: 11-16-2020, 05:56 AM
  2. Open Multiple Databases
    By neuroman in forum Modules
    Replies: 1
    Last Post: 03-06-2016, 10:25 PM
  3. One form multiple databases
    By Kennyej in forum Forms
    Replies: 6
    Last Post: 07-12-2015, 11:37 AM
  4. Multiple databases and 1 switchboard
    By makelesik in forum Database Design
    Replies: 2
    Last Post: 01-05-2011, 09:44 AM
  5. VBA SQL Query of Multiple Databases
    By VBA_Rookie in forum Programming
    Replies: 0
    Last Post: 12-02-2008, 12:32 PM

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