There are several valid reasons why I. T. Departments are reluctant to allow MS Access databases in their environment. There are equally valid reasons why users consider MS Access to be a good solution for their organisation.
I should state that I do work with MS Access databases and have done for over 25 years. I have also worked in and managed I. T. Departments. That enables me to see both sides of the fence. I sympathise with both sides, and I appreciate each sides arguments. Herein is each sides arguments and a comprise solution which can appease both sides.
The I. T. Departments view
I.T. Departments dispise MS Access. Most do not roll it out with MS Office, even though it is included in the subscription. This loathing is not without good reason.
Security
Security in this context does not mean viruses. I am talking about security of the data. With an Access database the data can easily be stolen. The backend of the database, the part that stores the data, is kept on a file share, which must be accessible by all users of the database. Anyone of those users can copy that database file onto a USB key, or upload it into some form of cloud storage, or simply email it to themselves. Once the database is away from the organisation any number of tools can be used to easily bypass any password security. Then all of the data is compromised. Every name, address, phone number, date of birth, or email address of everyone kept in the database as well as anything else stored in the database can be copied. It could then end up in a data dump online or sold on the dark web.
No organisation ever wants to have to publicly admit to having personal, client or staff information breached, but that is exactly what legislation {Australia} requires.
This whole database copying, is a lot more difficult with an sql server. The hacker would need to gain access to the sql server, port, database, account, and password. Most likely it was also be logged.
Garbage in / garbage out (G. I. G. O.)
The design of a database by untrained staff can convert useful information into meaningless garbage. Good database design involves splitting data into tables and fields which are linked together by joining ID numbers. This enables the data to be easily, searched and retrieved. It should limit the amount of duplicate entries, such as picking suburbs from a list, rather than typing in a suburb each time it is used. This ensures that suburbs are never miss spelt and also allows all suburb specific searches. Suburbs is but one example, but it would also apply to a list of professions, car manufacturers, parts, any list.
Support
The vast majority of I.T. staff cannot support MS Word, let alone MS Access. I.T. staff are not software developers or computer programmers. I.T. staff look after networks, printers, file servers, patches, user accounts and security . They rarely concern themselves with applications and if they do it’s only to either reinstall or patch them. The last thing they want to have to do is support some database, which has become mission critical, that was created by a now ex-staff member, especially when they have zero or limited skills in MS Access.
The arguments of the MS Access database creator.
Creators love MS Access. For many, MS Access is the first time they discover the ability to create a program. Some might go on to write code in VBA, but most will be content to drag and drop objects, to create forms and reports.
Simplicity
MS Access is very easy to use. It has templates for many applications already created. It can be very quick and easy to put together a small database even with no training, or just watching internet how to videos.
Free
After paying for MS Access, which is included with most MS Office subscriptions anyway, creating the database is free. Employees think they are doing the organisation a huge favour by saving them development costs.
Get what you want.
The staff member creating the database gets the application that they want, that is, if they have the skills to build it. Usually when purchasing software, there are some trade-offs and the office might have to slightly alter the way they work in order to comply with the software, but when building a database with MS Access the creator can get what they want.
Can be altered as time goes on internally
A huge benefit of creating their own database is that as the offices needs grow, the database can be reprogrammed by the same internal staff that created it.
So is there middle ground that keeps both the I. T. Department and the database creator happy?
The good news is yes there is a middle line. The following recommendations will appease the I.T. Department and allow the database creator to develop their own DB.
Training
First up, the database creator should be enrolled in formal training in at least Introduction to MS Access and intermediate MS Access courses. Only after this training is complete should the creator start designing the database. The database design should be run under the eyes of an experienced database developer to ensure the database table structure is valid. A good computer training organisation will provide this as a free service with the purchase of the training courses. This will reduce the problem of garbage in/ garbage out. Now, fully armed with the knowledge and skills of how to create an MS Access database, programming can begin. (Happy creator)
The Backend Database
The database backend should be stored on the departments SQL server. MS Access can get its data from the sql server with little to no program modification. (Happy I. T. Department) This will also make the database faster. (Happy developer and users) and if the queries that underlie the forms are tightened up to select only a single record per form, very fast indeed.
Professional conversion (optional)
At this point having saved a great deal of money by designing the database internally, the entire database could be given to a professional software developer, who can convert the MS Access database front end to an application. This can be inexpensive, as some developers have tools to convert MS Access databases to applications. Ideally if a SaaS (Software as a Service) developer is sought, there is a smaller annual subscription fee, but this means the application will be supported into the future, as the SaaS developer has an interest in not just maintaining the application but continuing to improve it and add new features. This makes the I.T. Department ecstatic as they do not have to support it. The database creator can continue to have input into the future of the application, but should they leave the organization, the application will still be supported and maintained by the SaaS developer.
Deployment
Once the database is completed, the backend can be given to the I.T. department to upload on their SQL server. The front end can be installed, or copied to, the end users computers.
Summary
Microsoft Access can be a very useful tool for an organisation, but it can also be extremely dangerous. Limiting its use to trained staff, using a SQL server for the back end and working with a professional SaaS developer removes the risks, while keeping both the I.T. department and the database creator satisfied.
