Discrete Modular Design (1 Viewer)

[Thales750]

How do you feel about databases being broken down into separate frontend files for different real world department's?
Also being able to load them from within the database?

Obviously access to these separate frontends would be controlled by login privileges.

 

[Josef P.]

The advantage would be that the individual frontend file is smaller.
The disadvantage: there are certainly code modules that are used in several frontends. Modifications must then be transferred to all affected frontends. Or you can design a code library for this.
Maintenance is definitely more complex with several frontends.

It doesn't matter for the user.
You can also design a frontend with all functions so that the respective employee only sees what they need.

Advantage of only one frontend: you can only activate the editing rights for the forms via database entries and do not have to make an extra frontend update if, for example, group X should now also have access to the customer list.

Also being able to load them from within the database?

This is certainly possible with a starter application.
But why burden the database with binary chunk transfer?

 

[The_Doc_Man]

Thinking about this from a data-locking viewpoint, having separate non-identical FE files doesn't change anything at the Windows level or at the Access level. I see no issue UNLESS someone opens two different but cooperative FEs on the same machine at the same time, since both FEs would be opened by the same user ID. But they would be opened by different process IDs, so there would be potential for confusion. Potential, but not necessarily a guarantee of a problem. However, inter-process interactions could get interesting.

Thinking about this from a maintenance viewpoint, you now have multiple extra files to maintain when you have to change something. To me it just adds more work by adding more "moving parts" to be managed. It adds "clutter" and offers a far greater opportunity for something to "fall through the cracks." Careful - one might say nearly "obsessive compulsive disorder-level" - configuration management could control the clutter factor - but the easier way to address clutter is to never have any in the first place.

Thinking about this from a site- or project-security viewpoint, extra FE files mean more things to put through a security certification process, particularly if the LAN is set up with different security zones but users of this database must cross one or more zone boundaries. Modern firewalls have ways of blocking things selectively that are chilling in their specificity. It would all be MSACCESS.EXE, I suppose, but firewalls can call out strange connections based on the relative paranoia of the site's security manager. Here, the problem - if it occurs - stems from an overly zealous IT person detecting two different connections from two different process IDs running the same base program (Access) to access the same BE file. Just remember that all IT security managers must have taken college course Paranoia 101.

 

[CJ_London]

Personally I see no benefit is separate front ends accessing the same back end.

I can see an argument for a HR front end with a HR backend for managing HR processes and a separate shop floor front end with a shop floor back end managing shop floor processes with (perhaps readonly) access to certain tables in the HR back end (could be a query 'view') for relevant personnel data.

In sql server this would probably be one server storing different databases for HR, shopfloor, etc

 

[Pat Hartman]

Separate FE's are fine as long as the job functions don't overlap. It will be inconvenient for users to have to open multiple separate FE's to perform their jobs.

Whether this gains anything or not depends on how large the app ends up being. The smaller FE is better than a larger one.

@CJ_London I'm pretty sure the apps we are talking about share data. When I was at Pratt & Whitney, one of their big problems was the fact that they had more than 25 Part master files. Most had similar column structures. Some had additional columns needed for their own functions. But many had conflicting data.

I put together a plan for moving toward a consolidated Part master so that we could get rid of the multiple maintenance functions each group had to perform to add/change Part information as well as the data anomalies that multiple change sources caused.

For this multi-functional application, you can have one big BE with one big FE, one big BE with multiple focused FE's. Creating multiple BE's with some overlap will be almost impossible to create efficiently. A and B use 1, 2, 3. C and D use 1,3,4. E uses 2, 3, 4, etc. Only tables in the same physical BE can have RI enforced so how would you enforce RI if RI was required between 3 and 4 but 4 was not in the same BE as 3?

 

[Edgar_]

How do you feel about databases being broken down into separate frontend files for different real world department's?

It makes sense.

Also being able to load them from within the database?

Not sure what this means. Opening Sales module from Human Resources module?

Obviously access to these separate frontends would be controlled by login privileges.

This is a business rules thing, I believe.

If you know your way around Access, programming in general and VBA, you can do whole lot of cool things. If you're a beginner, Access lets you do a lot already, but nothing stops you from trying to implement the modular approach, it'll be challenging first, but you'll learn a lot and will be able to take that with you to other platforms.

 

[CJ_London]

I'm pretty sure the apps we are talking about share data.

OP has not said since they are talking about splitting databases into separate front ends - which isn’t very clear - databases are the back end.

 

[jdraw]

Thales750,
Is this a hypothetical to get some ideas from others, or a serious design consideration? I agree with comments , and it is a bit thought provoking. The typical issue we see with front-ends is where user-developers build and use a single front end----and do not have individual FE on each PC.

 

[Pat Hartman]

OP has not said since they are talking about splitting databases into separate front ends - which isn’t very clear - databases are the back end.

It was the BE you were talking about splitting. That is why I commented. Splitting the BE, although not impossible, is a much more difficult task due to RI only being enforceable within a single BE. You cannot enforce RI across BE's and you certainly don't want to duplicate the data which is why I mentioned the P&W project.

 

[CJ_London]

If you read my post I was talking about linking to reference a few tables or queries not the whole database

one of my clients I support has 6 separate apps, for example one for managing purchase orders, another corporate documentation and another project management. The corporate documentation app needs some data from the other two whilst the purchase order app needs some information from the project management app. All they do is treat the data from the other apps effectively as lookups.

They don’t edit, add or delete in those tables/views. The personnel don’t use more than one app since they are in different departments with different tasks to complete

so to paraphrase what you are suggesting you would have one back end with tables for finance, marketing, hr, production, sales, etc? Might do that if I was creating a data warehouse

 

[Pat Hartman]

so to paraphrase what you are suggesting you would have one back end with tables for finance, marketing, hr, production, sales, etc?

No, that isn't what I said. I said the opposite.