Front end dimension

[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.

 

[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

 

[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 ...

 

[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 | An initiative for more professionalism in software development

An initiative for more professionalism in software development Professionalism = awareness + principles Software development needs professionals. But what are professionals? People who earn money with software development? No, we think there is more and something else to it. Professionalism in...

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.