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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
>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.
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.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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.