[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

MS Access best practices -- at what point do YOU decide to split an application into multiple frontends / backends

Let's be clear.
I am not talking about splitting an mdb file into front-ends and back-ends.
That was done long ago, and the back-end is on SQL Server.
What I am talking about is when and why you have looked at a massive, monolithic app and said
'Hmm, it's time to break this puppy up into a bunch of different applications because its HUGE!'

I'm beginning to go north of 150 forms and 150 reports.
Eventually, I may hit the ~1000 limit for the number of permitted modules.
All the data hangs together and relates -- it's just that, like any well-designed database app, it tends to subsume every business function it comes across.

You get an application going that tracks the central business activity, customers and employees, and soon every data-centric activity has strong relations with the app--and the app gets extended, and extended, and extended...

How and when did you say 'stop' and begin to break functions out into separate applications?
0
Nick67
Asked:
Nick67
  • 6
  • 3
  • 3
  • +4
8 Solutions
 
bonjour-autCommented:
Hi Nick67,

I am not sure, wether the Access area is appropiate for this Q. as it can occur with various monolithic monsters.
Many companies earn good money on building and maintaining monolithic monsters.

Best starting point for a change is an extension request from an department, which would add another additional dimension to your monster (in terms of compexity or time efforts). At a customer of mine this was the wish of sales administration for a much more flexible calculation programm (for offers) with connection to the existing core ERP plus a completely new CRM integrating these new offers and existing ERP-orders. We ended with leaving the existing ERP unchanged and adding a seperate application using the same I-Series server as database provider. The new solution could evolve fine naext to the old ERP system. They use it meanwhile for 8 years and are happy. Of course this is just a rough sketch of a real world example.
0
 
als315Commented:
I always try to analyze users and usually have some front-ends with one back-end. In most cases user don't need all forms and reports. A can have  sometimes) one huge front-end for myself, but  for each group of users I prefer to prepare front-end with limited functions. In this case I can think less about rights.
The other possible reason for splitting - group of "reporters" - no new data, only analysis. If base is extra huge and reports take a lot of time, I prepare data at night with sheduled queries. In this case I can have separate back-end for this group.
0
 
Nick67Author Commented:
:)
I am not sure, whether the Access area is appropriate for this Q
Since my application is built in Access (lots of objects & ~110K lines of code) it is Access folks' experience I want to tap into.
The backend is in SQL Server.  The data is itself not massive, ~1GB, and the number of tables at ~150 is nothing that should cause the server grief.  Still, opinions about why, when and how you'd decide to partition a backend into multiple .mdf files would also be welcome.  Hence the SQL Server topic area.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
bonjour-autCommented:
Of course data and table amount is not a real concern for Access frontend with SQL server backend.
But unnecessary complexity may become an issue. I am currently rewriting a 10 year old application, which we could continue for some more years. As the application has grown in the years, looking back at some structures shows room for streamlinig and modularisation.
Doing that in a phase, where no time pressure is present for implemetation of urgently needed extensions will give you a qualitiy approach for the next 10 years. Maybe you will find some areas, where some modules could be implemented directly int the cloud or in HTML5/PHP/XSLT. My most recent new application is a combination of local access application and native HTML5/PHP/XSLT web-module. The advantage of modularisation is that the modules have less complexity and can concentrate on the strength of te specific platform.
0
 
Nick67Author Commented:
My most recent new application is a combination of local access application and native HTML5/PHP/XSLT web-module.
That is what is coming next as the next 'must have' set of features (not the cloud, but web presentation of data).
I have tried doing that in ASP.Net using VB.Net (can't stand curly braces, so let's not talk C#)
It was a very painful learning curve as a webpage is radically different in event terms than an Access form is.
Namespaces.  Imports. .ToString()  Extremely complex to get simple things done with adequate performance
What kind of learning curve did PHP have?
0
 
bonjour-autCommented:
As i did it in small portions over the time, i have found it not difficult to absorb, especially as there are legions of good tutorials and examples out there.
I would recommend to avoid frameworks for the start, but go from scratch with simple tasks.
Data representation is sort of philosopy. I personally have decided for XML-files. XML is extremly flexible and you can handle it in a simple manner (PHP simple XML) or in a more complex but astonishing effective metod (XSTLT transformations)
Further i have decided against client side java script, after having it used for ajax communication and client side form-data-check i the past. Meanwhile I personally surf for security and privacy reasons with a java script blocker, so my applicatins shall do without java script too.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Couple of random thoughts...
One of the dangers of splitting up your front-end is that if changes occur in tables or common functionality, now you have to maintain it in multiple front ends.
How homogenous are the groups that use this thing?  It might be easier to table-drive user priveleges by maintaining a table of users and groups.  Read the user's network ID on login, figure out what group they belong to, and show/hide forms and funcationality accordingly.

That way it's still one behemoth, so easier for you to maintain, but looks to the user like a more custom app.
When I look at a massive, monolithic app, I usually think to myself 'Someone effed this up bigtime, which usually leads to job security for guys like me cleaning up stuff like this.'
How much data on this thing is considered confidential, such as HR data that Purchasing doesn't need to see, and is a pain in the butt to lock down in Access linked tables?
How much of the reports in Access are relative one-offs, where a user can enter a couple of parameters, hit a 'Run' button, and bammo the report generates?  If a lot, spinning up an SSRS server and writing the reports in SSRS with a SP/view as the data source is a viable option.
How fancy are users wanting reports?  The Microsoft Power BI toolset goes way beyond what I remember of Access report funcationality, so that may be an option as well.
0
 
SimonCommented:
I tend to go to multiple front-ends and a single MSSQL backend, with access to just the appropriate backend data for each front end/user type/individual.
I've never got to 150 forms or reports or 1000 modules in a single app. I try to make objects as generic as possible and use parameters, filters, tab visibility etc to make the same form or report cater for a range of specific cases or usage scenarios.
I tend to go for departmental front-ends that focus on sales, operations, finance, HR etc. I've found that most of my regularly used code fits into a limited number of 'libarary' modules that go into almost every database I build. I can live with the overhead of having some unused code in any database for the sake of having standardised, re-usable modules.
Having a single back-end is good for ensuring consistency and simplicity, though new projects sometimes start in a devDB database on the SQL server (that uses cloned tables from the PROD db) before being fully integrated.
0
 
Nick67Author Commented:
Read the user's network ID on login, figure out what group they belong to, and show/hide forms and funcationality accordingly.  That way it's still one behemoth, so easier for you to maintain, but looks to the user like a more custom app.
That's already on the go.  I've had Dev Ashish's  ReturnUserName and ReturnComputerName on the go for quite some time, and the comboboxes that drive the main menus to get tailored accordingly, and objects nicely refuse to open if you aren't in the right context.

•When I look at a massive, monolithic app, I usually think to myself 'Someone effed this up bigtime, which usually leads to job security for guys like me cleaning up stuff like this.'
Thanks!  But that's the point of the Q, at what point do you draw the line between 'This is core functionality' and 'Let's break this out into a separate front-end and if that forces users to switch between apps, it won't be that often and they'll just have to suck it up.'

spinning up an SSRS server  SQL Server Express Edition, so SSRS isn't on the table.
Pity.  But management'd lose their minds about the expense of SQL Server CALs without a clear enough ROI.

I've never got to 150 forms or reports You can get there surprisingly quick.  A fully normalized table with 4 or 5 foreign keys can easily lead to a bunch of forms just to get the data in.  My main table at this point participates in 48 one-to-many realtionships, with possibly more on the horizon.  Hence the question.
0
 
SimonCommented:
>A fully normalized table with 4 or 5 foreign keys can easily lead to a bunch of forms just to get the data in

I suppose it could, but for data entry I'd be tending to use a single form with a multi-table query as the recordsource for the form and for recordsets. Your environment may be very different from mine, but I don't usually create a form for every table, just a form for every functional task. OK, I may have a few subforms, but not one per table.

>the expense of SQL Server CALs
Depending on your number of users, you might be much better off with per-core licencing for MSSQL.

>The backend is in SQL Server.  The data is itself not massive, ~1GB, and the number of tables at ~150
That's tiny. Looks like you kept it all tight :)
No reason to split the backend then unless you have totally disparate sets of tables. Even sticking with SQL Express you have lots of room for data growth.
0
 
Nick67Author Commented:
I generate graphs.
Lots of graphs.
Graphs where I have to average data points because an Access chart will only deal with 4000 points
(remind me to put that in to the discussion of what Access needs
But for those I looked at what had the potential to quickly become billion row tables and said:
'Nah, we'll leave those as csv files and import the data dynamically as needed'

Pictures.
Lots of pictures
300K and growing daily, ~200 GB these days.
Those stay in folders with the paths stored in a table.
So the data stays pretty tight.  There's only one memo field in the whole thing.

But @mx was saying he now loves the Nav Pain.
A flat file listing with as many objects as I have is just useless.
But the only way to have less objects, is well, to have less objects.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Only way I've ever been able to do this is analyze how the userbase is using the app, and make decisions from there. For me, if there is a "block" of functionality that is rarely used, or used by a small group only, then I'd certainly consider cutting that into a separate FE. For example, if only Accounting needs to work with Invoices and such, then I'd consider moving functionality involving those objects to a separate FE.

I believe you already know this, but regarding the Nav Pane - if you have objects which are "grouped" together, the Nav Pane allows you to create Groups which could contain those object, and perhaps make it easier to maintain. I've found that to be useful with larger apps I've taken over.

But in the end, it's all about how the app is used, and only you (and the user base) can determine that. In the past, I've added code to the Open event of my objects to log when they're being launched, and ran that over the course of a few weeks/months. I was always surprised what I learned - things I assumed were heavily used ended up being rarely used, and things I assumed were almost never used received heavy usage. So I learned not to "assume". Of course, you're working in the company daily, so you have a much better grasp than me, who would monitor from a remote location, with no involvement in the day-to-day.
0
 
SimonCommented:
But @mx was saying he now loves the Nav Pain.
A flat file listing with as many objects as I have is just useless.
But the only way to have less objects, is well, to have less objects.

+1 for the Navigation pane, after about two years of getting used to it. Custom groups are good, but I find I mostly use the object search bar to filter the list down to the few objects of all types I need to see at that moment. It helps if object naming convention is adhered to and you have commonality between the tblXYZ, qryXYZ, frmXYZ, rptXYZ, modXYZ names where appropriate.

I also try to keep the number of objects down by making each object as 'smart' and useful as possible (as I alluded to above). There is a also an argument for having a larger number of simple, lightweight objects, but I hate replicating updates to multiple variants of of similar form.
0
 
Armen Stein - Microsoft Access MVP since 2006Commented:
I'll add to SimonAdept's comment about how to decide when to split a front-end application.  If you have different groups of users (say in departments, divisions, functional groups) that mostly use just a portion of the FE, then you have a candidate for splitting.  On the other hand, if a particular user regularly has to open two different FEs to do their job, then you've split too much.

If you split the FEs, make sure that your common modules are identified and named, so that your common code doesn't branch.  You should be able to replace all of your common reusable modules from a central master without worrying about it.

Cheers,
Armen Stein, Access MVP
0
 
Nick67Author Commented:
In summary:
If the backend hangs together well and is not stupidly massive, there is little reason to ever split a backend.
The front-end decision to split depends very much on the functionality and usage patterns that the app develops.
If dictated by a modular set of usage by particular groups, a partitioning of the front-end may make some sense and lead to tighter applications.  if no such patterns really exist then despite the first impression of 'Someone effed this up bigtime, which usually leads to job security for guys like me cleaning up stuff like this.' the final impression may just be that, 'Wow this is huge, but there really isn't a good way to break it apart that will lead to improved productivity to the users and simplicity for the developers.  And depending on how much functionality needs to be shared over multiple front-ends, the decision to split may lead to increased complexity for the developers, which is not the idea at all.
0
 
Nick67Author Commented:
Thanks to everyone who chimed in.
I am still on the fence (and therefore staying with the status quo) about my own main app.  I have a two other apps that are very much a specialized subset on functions.  I am now in the process of adding a fourth, but consolidating the backends together in a single .mdf

Thanks,
Nick67
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 6
  • 3
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now