Solved

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

Posted on 2015-01-14
16
218 Views
Last Modified: 2015-01-25
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
Comment
Question by:Nick67
  • 6
  • 3
  • 3
  • +4
16 Comments
 
LVL 18

Assisted Solution

by:bonjour-aut
bonjour-aut earned 125 total points
Comment Utility
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
 
LVL 39

Assisted Solution

by:als315
als315 earned 75 total points
Comment Utility
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
 
LVL 26

Author Comment

by:Nick67
Comment Utility
:)
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
 
LVL 18

Assisted Solution

by:bonjour-aut
bonjour-aut earned 125 total points
Comment Utility
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
 
LVL 26

Author Comment

by:Nick67
Comment Utility
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
 
LVL 18

Expert Comment

by:bonjour-aut
Comment Utility
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
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 75 total points
Comment Utility
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
 
LVL 18

Assisted Solution

by:SimonAdept
SimonAdept earned 75 total points
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 26

Author Comment

by:Nick67
Comment Utility
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
 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
>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
 
LVL 26

Author Comment

by:Nick67
Comment Utility
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
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 75 total points
Comment Utility
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
 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
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
 
LVL 9

Assisted Solution

by:Armen Stein - Microsoft Access MVP since 2006
Armen Stein - Microsoft Access MVP since 2006 earned 75 total points
Comment Utility
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
 
LVL 26

Accepted Solution

by:
Nick67 earned 0 total points
Comment Utility
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
 
LVL 26

Author Closing Comment

by:Nick67
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now