Business System Consultation Center - Our Business System ColumnVol.125 2022.03.01 Takahashi Minoru

The advantages of in-house systems

Thank you for your interest and continued support.
This is Takahashi from the Marketing Plan Research Laboratory.


In this column,
I will share my thoughts and predictions
I will share my thoughts and predictions on this topic.


Based on my experience, there seems to be a perception that Access cannot be used to build advanced database systems.
In fact, I’ve even heard such opinions from fellow professionals (system developers).
I can state with certainty that these are undoubtedly prejudices.


■Access is the standard for in-house systems


When it comes to
, Microsoft Office Access is the first thing that comes to mind.
This is only natural, given the market share of its programming language (VBA) among in-house IT staff.


■The reason for this prejudice lies in the fact that “far too many people have used Access”


I don’t have exact statistics or data to back this up, so this is just an estimate, but
there are likely
is likely quite high.


Depending on the Office edition, Access is often installed without users realizing it, and
and it shares many similarities with Excel VBA (macros) when it comes to programming.
and because it allows for easy creation of queries, forms, and report objects via the GUI,
makes it by far the most "accessible" among the many programming languages available.


However, Access VBA has its own unique “nuances”
—that is, several challenges that must be overcome to build a system effectively.


It has a wide appeal and is easy to get started with, and it is so widespread that it can be considered a standard.
and many in-house IT staff choose it unconsciously or out of convenience,
but just like any other language, it is by no means easy to master
That is the nature of Access VBA.


So many people have dabbled in it
and far too many have given up on it
and as a result, there are far too many people spreading baseless rumors.
That is likely the root of the negative perception surrounding Access.


■Microsoft is also partly to blame


In this case, it is not fair to blame only those who mishandled Access.
Access has constraints that are unusual compared to other languages,
and there are many factors that hinder new in-house IT engineers and cause them to drop out.


・Irregular bugs caused by Windows Update
・Compatibility issues between versions and operating systems
・The 2GB file size limit (errors gradually increase once the file exceeds 1GB)
・Files can be easily corrupted when accessed by multiple users simultaneously
・Bugs inherent to Access itself (such as the MSBARCODE.DLL issue)
・Extremely difficult to break out of infinite loops or freezes


and so on; when working with Access, you must
manually (by writing your own source code),
and in this regard, one could argue that Microsoft is at fault.


■Key Considerations for Systems Built with Access


Since we’re on the subject, I’d like to share
I’d like to share some key points for building effective systems in Access.
I hope this will be helpful for those of you who are planning to try it.


・Always separate program files from data files
・Avoid using macros (though some macros, such as AutoKeys, are absolutely necessary)
・If you need to use queries, delete and regenerate them using VBA each time
・Do not define tables using binary fields
Combine with file sharing via File Explorer
(such as SMB or cloud storage)
・If the database is likely to grow large,
or if you expect more than 10 users,
design the system to use SQL Server or SQL Azure from the start
・Do not over-engineer the source code (do not overcomplicate the specifications)
Identify the core requirements and keep the implementation simple
・Keep componentization of commands and table normalization to a moderate level
・Do not attempt to implement asynchronous processing
・Do not mistake the limits of your own technical knowledge for the limits of Access
・Learn Windows OS alongside VBA


That’s all.
In this day and age, you don’t need to rely on books to learn Access.
All you need are the qualities of a systems engineer and access to the internet.
Let’s strive every day to become excellent maintainers of Access systems together.


That's all, Thank you for reading.

------------------------------

■ Previous / Next Column ■

＜＜＜ Next Column Vol.126 - Why does UTM interfere with your system? 2022.04.01

＞＞＞ Previous Column Vol.124 - Why does MS Access have a bad reputation? 2022.02.01

Back to Column List