As the title indicates, the microsoft office database product, Microsoft Access, is limited by the company itself so that it does not compete with other products such as SQL Server and C# program development, for example.

I explain:

The first limitation is the commercial one. Access is not sold alone, but as part of Microsoft Office and therefore an application made in Access cannot be distributed without first verifying that the customer has a valid license of the same Office as such. Microsoft does not license Access distribution to third parties. Note that there is no installer for something made in Access only. More than fifteen years ago there was one that had a very short duration.

The second limitation is that Access does not promote something as simple as the division between program and data. Everything resides in the same Access file. Hasn't it happened to you that when you have to update Access programs and reports you first have to "copy and paste" all the client tables in the new file with the updates? Of course, this can be solved if the tables are "always" created and maintained in a separate file and in another file all the rest of the programming is put, including queries, linking one to another table to table. But beware, the data file must always be in a subdirectory that does not change from user to user, read, it should not be in "My Documents", for example. It must be placed in a predetermined folder on disk C: or in a shared network folder, always the same, preferably accessible as "disk letter", that is, Q:\data for example.

The third limitation is that there is no system of events and triggers associated with the content of the data, as there is in other database systems. In almost all the others. For example, suppose that when the value of a piece of data exceeds a threshold, a code is required to be executed. Suppose further that the data is in a single Access file in a network shared folder, let's say it has a name Q:. Let us also suppose that there are many Access files with programs that use this shared file, and that one of them increases the mentioned value and the threshold is passed. How to execute an associated code? That is precisely the needs for the events and triggers associated with the content of the data.

However, Access is practically the only one that works with almost all other database systems, through access via external data linked even by ODBC.

In conclusion, in Access you can do many things, but it has distribution limitations, you have to separate data from programs, you have to be meticulous with both local and network locations, and there is no system of events and triggers associated with the content of the data.

Have you ever wondered why in Office 365 you can program in JavaScript and in the local Office it is not possible?

I hope I have been useful,

Octavio Báez Hidalgo.

No thoughts on “Microsoft Access as a database is limited by design and what to do.”

Leave your comment

In reply to Some User