X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Linking Be's And Fe's / Shared And Unique Data Between Db´s, Access 2016    
post Nov 27 2019, 11:43 AM

Posts: 3
Joined: 11-June 19

Hello everyone!,

even though this is my first post here, Ive been visiting and learning alot from this community for a while now, so firstly i want to thank the community for all the content/discussions smile.gif. Secondly, let me know if this post wasnt supposed to be here or if the structure/question wasnt well explained, im still an Access rookie smile.gif.

Ive been developing a Access Database for a while and i have a question related to Linking FE to BE.

Here is some background on the Project:

The database is going to be used by different departaments (Group 1, Group 2, Group 3, etc).
Each departments has its own nomenclature/name structure therefore they have some table with different values/names, i will call it "Group Specific data".
Some data are equal for all the departament, i will call it "general data" (Users, employes, etc).
The data structure/tables relationship are the same for all departaments.

ATM Ive develop just one Database as a "Template" for all departaments.
My ideia is the following:
1. Make some copies of this "Template" - DB_Group 1 ; DB_Group 2; DB_Group 3;
2. Split the databases into FE and BE - DB_Group 1 FE and DB_Group 1 BE ; DB_Group 2 FE and DB_Group 2 BE ; DB_Group 3 FE and DB_Group 3 BE
3. Make a copy of one BE to store shared data - "General data BE"
4. Use the link manager to link the tables from DB_Group 1 FE, DB_Group 2 FE and DB_Group 3 FE which are for "general data" to the one "General data BE".

Ive the tables relationship and Structure attached

My questions are:

1. Am I going the wrong way here? or is there a easier/better way to solve this kind of problem?
2. Would using the link manager on the FE´s be enough? or should use the "Import or Link Dbase file"? what are the pros and contras?

Ive a feeling that this solution may not work when implemented or it will be just kind of confusing to manage this in the future.

Thanks in advance and im happy to hear your Input/Ideas.
Let me know if u need anything else from me.

Attached File(s)
Attached File  TablesRelationship.png ( 132.09K )Number of downloads: 15
Attached File  Strutcture.PNG ( 120.64K )Number of downloads: 7
Go to the top of the page
post Nov 27 2019, 12:05 PM

UA Moderator
Posts: 77,494
Joined: 19-June 07
From: SunnySandyEggo

Hi. Welcome to UtterAccess! welcome2UA.gif

I think what would be important for us to know is what is the business about? How would each department use the data. For example, if you have a common source of employee data and the HR/Payroll department will be adding/entering pay information that you don't want the Floor department to see, because all they will be doing is to enter task assignments, then you can either restrict the payroll data or create a separate backend database for it. Either choice is workable - just depends on your skills and comfort level.

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
post Nov 27 2019, 07:09 PM

Posts: 3
Joined: 11-June 19

Hi DBguy,

first thanks for the reply! (btw your answers from past posts in the forum really helped me a lot smile.gif )

The objective of this database is to track/plan components necessary to a number of Projects.
Each department runs a couple of projects in parallel and all the projects have the same structure (relationships between the tables).
Each project has many components, which has several attributes (the necessary attributes/structure for each project is the same).
For some internal reason, which is out of my power to change, each department has its own naming convention for some attributes.
Some components/project attributes are shared between them, for example employee data + where they work/development team/etc.

In this case all the data being entered is common knowledge, so there is nothing that the people who are using it aren’t supposed to see. On the other hand, there are some things that just some people are supposed to edit. For that reason I´ve a user permission system that i´ve actually learned here in UtterAccess 😊.
Would be in this wise to create a BE with all the shared information and delete the respective tables from the BE from each departament? For example:
Delete tblEmployee from DB_Group 1 BE; DB_Group 2 BE; DB_Group 3 BE
Link the tblEmployee on DB_Group 1 FE; DB_Group 2 FE; DB_Group 3 FE to General data BE
Could you elaborate on the “Either choice is workable - just depends on your skills and comfort level.”
I don’t understand which factors I should be considering to make a decision or how should I look up for some material which would help me make this decision.
Im gonna do some research on the differences between link manager on FE/linking tables between BE and so on and try to understand it better. Let me know if you know some material related to this.

Go to the top of the page
post Nov 27 2019, 07:15 PM

Posts: 633
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA

Hi, was looking at your ER diagram and Im not fluent in German. Does "Umfang" mean scope, as in Project Scope?

I am not sure if creating specific tables for specific departments is a good design, especially if you plan to consolidate certain data. Cant you use type and sub-types in one table for all departments? You can also include department-specific fields in one table, whether the other depts. use them or not. Depending on which department users log in, you can hide or make visible certain fields that apply to each specific department, or if that gets too hairy, create separate forms for each department.

My two cents.
This post has been edited by FrankRuperto: Nov 27 2019, 07:37 PM

Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
post Nov 27 2019, 07:18 PM

Posts: 3
Joined: 11-June 19

Im also not fluent in german but in this case is more like component smile.gif

In this case the tables are the same what change are the values, for example on tblSetliste(SETKurzel;SET;Beschreibung;KEFA)

for groupe 1

for groupe 2 this case wouldn´t exist, so i didnt want them to be available on the "drop-down". as many other examples

Those departament specifc information are just for their own departament relevant.

If at some point we need a report compiling all the projects (which doesnt happen) we could compile the reports in excel, as i already have a build in export function.

Is my thought process here right? as i mentioned before im new to this smile.gif
This post has been edited by lumaximo131: Nov 27 2019, 07:47 PM
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    19th February 2020 - 05:30 AM