Turing's Man Blog

Does MS Access have anything special to offer?

Bookmark and Share

What do I like in MS Access? Well, it will shock potential readers that are not familiar with this tool, but this is: openness (of course not in sense of being Open Source product, but from the scope of the possibilities to integrate with other vendors’ database products). Really. We used to think that Microsoft technologies are closed by design, that this company used to protect its own business by creation of new, closed “standards”, allowing users to easily integrate Microsoft technology based solutions only with themselves, etc. Internet is full of such complains on the forums. From the other hand, in most cases, such arguments are not fair or at least are not checked deeply enough. MS Access is a good example of this situation. Let's see.

First of all, it’s always hard to easy answer: what is MS Access? It’s not just a database engine (which in fact is only a part of the whole software package and according to the version is called Jet or Ace). It contains a full set of quite advanced and flexible tools for database design and development. Generally speaking, we have the basic objects, like: tables, queries (SQL statements in other words), forms and reports. Almost everything in MS Access is supported by very friendly wizards that can help in the most common tasks and are really usable for beginners. Although, at some level of advancement these wizards can make you feel really limited. Not a problem – you don’t have to use wizards for all your tasks.

Moreover, if you feel somehow confused with the functionalities given “by default”, you can still explore more advanced features like: built-in VBA (Visual Basic for Applications language with the full set of developer tools), Macros (to be honest I don’t like them and rarely find them usable, but still this is the question of personal preferences) and a possibility to easy upscale the database solution. Yes, we all know how MS Access Jet/Ace database engines are limited – although, most of us are just repeating opinions about this technology, but not checking the reality good enough to have their own, proven statements (well, it’s normal on today’s Internet forums). These built-in database engines are really limited and we shouldn’t expect too much improvements in this field in the future. These are good enough for the purposes someone designed for them. If we really need something better, our database is more mature, bigger, we have more users than at the very beginning, we’re processing much more data, we want to use advanced security options of stored procedures, then… We should decide to migrate the backend (let’s say – the data part of the whole solution) to the external, more powerful database engine.

The natural choice, which seems to be supported the most and provides easy to use wizard is, of course: MS SQL Server. We can use the full version of this database server or Express editions, which are free to use and distribute, yet with some technological limitations. However, this is not the only option to be considered for data migration. We can make use out of ODBC and migrate all our data very easily to one of the leading database servers, like: Oracle (10g, 11g) – if we want to mention the most popular commercial database servers used in enterprises or the most important Open Source players, like: MySQL (now belongs to Oracle, but fortunately remains GPL-ed, too) and PostgreSQL.

Backend migration is a very good option. Our data is then stored in much more advanced backend database. We can make use out of all the advantages of our database server, like: invoking stored procedures and functions, which are developed on side of the backend database and are executed by the database server itself (which means server side processing); using advanced security features; execution and changes logging/tracking; triggers; db-links for replication etc. We have much more efficiency for our solution, we have skipped Jet/Ace limitations, but still, we can use our familiar frontend. This is the part of the application developed in MS Access which consists of forms, reports and a set of queries. So, for our end-users who don’t want to learn anything new at the moment, this is a good news – you can use the application the same way like before, except, everything will work faster right now. Things are almost transparent if you’ll migrate to MS SQL Server, in case of ODBC connection to other database engines it depends on how complicated your project is, yet, it’s always possible to go in this direction with full success (and I did it several times).

At the same time, concurrently, the developers can start the migration of the fronted part of the application. This can be deployed against the data already stored in the “professional” backend database server. In such scenario, our application, which born in MS Access, became networked database (split to frontend and backend file placed somewhere on a network share), faced efficiency issues and any other problems as a result of limitations of Jet/Ace database engine… First became supported by external backend database server (which, of course, don’t have to be Microsoft based technology) and then, when the frontend was finally rewritten in other technology (any we can imagine from C#, through Java to web based like ASP.NET or PHP), changed to quite a… different story. A system nobody even suspects was born in MS Access one day.

So, please tell me, isn’t it unique feature of MS Access? Isn’t it a real openness? In my opinion it is. That’s why I like MS Access as a database related – Swiss Army Knife.

PS. I wrote some more recommendations on this subject here, here and here.

Bookmark and Share

Add comment

Security code