Front end dimension (1 Viewer)

[Andy74]

Hello,

I am running an Access application since more than 10 years. It has become a very large one in our organization but I would say very successful: about 100-120 users in different locations across the globe. All front ends are linked to a main SQL backend (read/write) and some other back ends (read only); each user with their own Front End on the local PC's. Users out of the local LAN connect with RDP to a terminal server. I am not a professional programmer, I am a mechanical engineer but I have always enjoyed Access and I found it extremely efficient. And as the main user I am updating and expanding the app almost weekly to new departments/functions in our company: it started from Sales then went into Technical dept, then Quality, After Sales, etc.

The accde file I distribute is now about 80 Mb, with the following number of objects:

Tables 318 (most of them linked, few local)
Queries 1027
Forms 218
Macros 38
Reports 51
Modules 23

Everything is running perfect but some of our IT guys are always complaining about MS Access is not a "modern tool": but when I say "ok, you can build the same front end yourself with PHP or what else" they come back with "well, it will take some years". So at the end I keep on developing with Ms Access.
I wonder: is there anything wrong in my approach? Any suggestion? Somebody is getting worried because a very critical tool is developed and managed by only one person.

I appreciate any comment.

Andrea

 

[plog]

Everything is running perfect but some of our IT guys are always complaining about MS Access is not a "modern tool": but when I say "ok, you can build the same front end yourself with PHP or what else" they come back with "well, it will take some years".

Sounds like you are working at every place I have ever worked as an Access developer. IT people love to crap and look down on Access but when you call them on it, it's all hemming and hawwing and outlandish estimates for replacing it. Then that 1 time out of 10 they actually try and take a piece of it on they can never get it right.

With that said, I don't totally disagree with the IT guys...

And as the main user I am updating and expanding the app almost weekly to new departments/functions in our company

This is where I think you are wrong. No new functionality, make IT implement anything new. Despite what I said above, they are better suited for it. The have better processes, they have better tools, more people and most importantly redundancy. No one person in IT will be a point of failure for that functionality. You sound like an enormous point of failure. I don't mean that negatively, I mean that if anything happens to you this whole Access thing crumbles immediately and everythign that relies on it grinds to a halt. That won't happen when IT takes something on.

Also, based on the number of objects in that database your organization has failed itself by letting this thing get this big. This is an emergency waiting to happen. I cannot believe one person maintains this. Especially when that one person self describes as " not a professional programmer, I am a mechanical engineer ".

I believe you need to cleave this thing or find a junior person or three to be in charge of parts of it. Ideally you give parts of it to IT to maintain and you keep others. Or if they won't get on board bring others up to speed on how it operates. Too much is riding on you at this point.

 

[Andy74]

Sounds like you are working at every place I have ever worked as an Access developer. IT people love to crap and look down on Access but when you call them on it, it's all hemming and hawwing and outlandish estimates for replacing it. Then that 1 time out of 10 they actually try and take a piece of it on they can never get it right.

With that said, I don't totally disagree with the IT guys...



This is where I think you are wrong. No new functionality, make IT implement anything new. Despite what I said above, they are better suited for it. The have better processes, they have better tools, more people and most importantly redundancy. No one person in IT will be a point of failure for that functionality. You sound like an enormous point of failure. I don't mean that negatively, I mean that if anything happens to you this whole Access thing crumbles immediately and everythign that relies on it grinds to a halt. That won't happen when IT takes something on.

Also, based on the number of objects in that database your organization has failed itself by letting this thing get this big. This is an emergency waiting to happen. I cannot believe one person maintains this. Especially when that one person self describes as " not a professional programmer, I am a mechanical engineer ".

I believe you need to cleave this thing or find a junior person or three to be in charge of parts of it. Ideally you give parts of it to IT to maintain and you keep others. Or if they won't get on board bring others up to speed on how it operates. Too much is riding on you at this point.

Thanks for your comments, much appreciated. Maybe I was a little bit extreme: in fact the IT guys "maintain" the SQL server back end and the backups. Moreover there is one person in my team that can look in the Access front end if needed but he doesn't make any new development.

 

[Minty]

We have taken over the management of quite a few of these types of projects, frequently when someone is coming up for retirement!
It can be quite a challenge learning about systems that have grown over 20 + years in some cases.

Frequently the same problem exists - a single point of failure.

 

[Pat Hartman]

Maybe, your IT people should take another look at Access as a development platform. They really need to come to grips with the difference between Access the RAD (Rapid Application Development) tool and Jet/ACE the closely tied desktop database engines. When you examine all the bad press you see regarding "Access", the vast majority comes down to the desktop engines Jet and ACE and don't have anything to do with Access the RAD tool at all.

Access does not have native web capabilities so it can never be used as an app like the forum we are using where strangers can log in and participate. Access works in a closed environment which is fine for all internal apps and by using Citrix or RDP it can support remote users quite easily and shouldn't be a drawback once you have Citrix or RD available.

Once you off load your data to SQL Server, "Access" is no longer tied to all the limitations of Jet and ACE. Instead of your max users being 255 (hard) or ~50 (soft), your max users is dependent on the number of seat licenses the company has for SQL Server so you could conceivably have thousands of users. Your database size is limited to whatever SQL Server (or whatever your RDBMS is) will support. Security is "internal" since Access is essentially an internal application and will run inside the firewall. There are still gaps in security but there are ways to tighten it up and only internal, authorized people have access so your exposure is much more limited than with a web app. Jon, the owner of this site is constantly fighting the battle with hackers.

Distribution of a new FE can be a little awkward to set up but the real downside to Access as the platform for a large application is its use of a database container to hold all objects. This restricts the number of concurrent developers to 1. There are ways to segment development tasks and then merge later so you can support a larger development team but they are complicated.

Given that single developer constraint, I would probably look as segmententing the FE to functional areas. They can still all share the same BE but separate groups would be responsible for updating different sets of data. You can create a master FE that has a menu that can access the functionality in different FE's seamlessly so the users workflow won't be impacted but maintenance can be more easily accomplished by several developers as a team instead of just you.

Your IT is correct to be worried about this application. You are a single point of failure and you could get hit by a satellite falling out of orbit tomorrow. It seems to have grown to be mission critical. There are ways to minimize the dependence on you but only if IT is willing to either replace the app entirely or open their minds and come into the Access world a little so they can provide meaningful support.

IT in every shop I have worked with has a backlog that runs for years. Access apps grow up around the infrastructure to support new business requirements that don't rise high enough in the IT backlog to happen for at least 3 years and you lose business opportunities if you can't move fast. Access is an EXCELLENT tool for rapid application development. If IT could understand that, they could even use it themselves for short term needs or temporary fixes until the permanent app gets fixed, etc.

If you have a good working relationship with IT, you can help each other to solve the problem of "you" being a single point of failure without giving up Access the RAD tool

 

[Pat Hartman]

It can be quite a challenge learning about systems that have grown over 20 + years in some cases.

If IT didn't turn its nose up at Access, this could be "controlled". IT should provide technical support and guidance for example. They can evaluate all new schemas and offer solutions. They can make company standards and guidance documents. This can help many novice developers working in the user departments to create better, more easily integrated/upgraded applications.

 

[Andy74]

If IT didn't turn its nose up at Access, this could be "controlled". IT should provide technical support and guidance for example. They can evaluate all new schemas and offer solutions. They can make company standards and guidance documents. This can help many novice developers working in the user departments to create better, more easily integrated/upgraded applications.

Thanks, in fact IT are the ones that many years guided me to using an SQL express backend which made everything much more solid. You gave a good explanation about the pro's of Access as a RAD tool, I will certainly use this to redefine a new strategy of software development within our company. I have a very good relationship with our IT, the only thing is that they never have time to "develop", they rather just maintain the infrastructure as it is.

 

[Gasman]

Thanks, in fact IT are the ones that many years guided me to using an SQL express backend which made everything much more solid. You gave a good explanation about the pro's of Access as a RAD tool, I will certainly use this to redefine a new strategy of software development within our company. I have a very good relationship with our IT, the only thing is that they never have time to "develop", they rather just maintain the infrastructure as it is.

That was the reason I had to create a DB for my dept for one of the UK four big banks. so I know where you are coming from.
However they did not support it when I was there. I do not know if they did after it passed governance, for which they charged my dept £11K

 

[Pat Hartman]

Many of the applications I have developed for large companies were to fill in the gaps of their ERP. ERP's are sold as "all things to all people" but the reality is that they are pretty poor at a lot of things and difficult to customize. So, instead of using the ERP functionality for drawing logs, order entry, etc., Access apps can fill specific requirements and get data from and feed data into the ERP.

 

[jacko6]

It never ceases to amaze me that an Access app can:

Provide all of the functionality required to meet a particular business need and the needs of users.
Be easily modified to meet new/changing requirements.
Be incredibly reliable when developed and distributed properly, often outperforming the stability of other very expensive technologies.
Have a satisfied user base (never underestimate the importance of this as it is not an easy thing to achieve).

And yet someone it IT who knows little about the app gives it a bad rap simply because it's an Access app. Usually they've never developed anything themselves in Access.

You are right to challenge them to deliver an alternative.

My background is IT and I shake my head at the huge amounts of money wasted on developing apps in the latest technologies, only for them to be unreliable and fail to deliver on the business and user requirements.

Of course there development situations where Access simply won't meet the requirements, but a working Access app doesn't need replacing just because it's an Access app.

 

[Andy74]

Many of the applications I have developed for large companies were to fill in the gaps of their ERP. ERP's are sold as "all things to all people" but the reality is that they are pretty poor at a lot of things and difficult to customize. So, instead of using the ERP functionality for drawing logs, order entry, etc., Access apps can fill specific requirements and get data from and feed data into the ERP.

In fact one of the functions I have implemented is "feeding" the ERP with the forecast provided by the sales people. Before this there was one guy getting excel files from them and feeding ERP tables manually, really a crazy thing ...

 

[Pat Hartman]

Keep in mind that it is always management that chooses to buy an ERP based on the sales pitch. The grunts are left to work around it.

 

[Andy74]

Hello,

thanks for all the comments received. Regarding front end dimension, it was actually the subject of the thread:

The accde file I distribute is now about 80 Mb, with the following number of objects:

Tables 318 (most of them linked, few local just as temporary tables)
Queries 1027
Forms 218
Macros 38
Reports 51
Modules 23

Are these numbers of any concern? I don't see any issue with Access and especially since version 16 I don't have any issue of front-end corruption and it looks very stable. So I assume the number of forms and other objects can still grow significantly without special issues? Do you have any experience with much larger front ends?

Andrea

 

[amorosik]

Hello,

thanks for all the comments received. Regarding front end dimension, it was actually the subject of the thread:

The accde file I distribute is now about 80 Mb, with the following number of objects:

Tables 318 (most of them linked, few local just as temporary tables)
Queries 1027
Forms 218
Macros 38
Reports 51
Modules 23

Are these numbers of any concern? I don't see any issue with Access and especially since version 16 I don't have any issue of front-end corruption and it looks very stable. So I assume the number of forms and other objects can still grow significantly without special issues? Do you have any experience with much larger front ends?

Andrea

My experience, with Access 2013 + db server:

500 forms
110 modules
120 queries
600 reports
600 tables (linked, no one internal)

And it's all fine

 

[ebs17]

Are these numbers of any concern?

The specifications for Access should be consulted for this assessment.

However, numbers alone do not say that much.
The implemented programming would be interesting: Do you have variable forms that can be used multiple times? Can queries be parameterized to make them more universal? With such measures, numbers can be reduced.

Clean Code Developer | Eine Initiative für mehr Professionalität in der Softwareentwicklung

An initiative for more professionalism in software development Professionalism = Awareness + Principles Software development needs professionals. But who are Professionals? People who earn money with software development? It's more than that. Professionalism in software development is nothing...

clean-code-developer.com

few local just as temporary tables

Temporary data and temporary tables tend to bloat the database and create additional problems.
So if something more than minor happened, I wouldn't have temp tables in either the backend or the frontend, but I would move those temp tables to a local temp backend and work with appropriate links in the frontend. With the end of the session, the local temporary backend can then be terminated in one piece.