A Technical Article !!!!

Donno what has inspired me in writing this piece @!###@# .. but nevertheless I find it worth a mention here ..

MS ACCESS – TIPS TO USE IT

In technical jargon, I stand a “release” old now, however I faced quite a lot of flak for my inane and excessive query usages during my last project. As I referred to biblical google, I got a better idea on which databases to use, and most importantly, how to handle them.

One such study was forced on, perhaps the patch of our release made me to google around. This study is dedicated to enlighten the mishaps which we faced with MS Access, and correspondingly its misuse inspite of the presence of MS SQL server.

MS Access is a desktop application, it behaves more or less like an excel workbook. It is a cheap and easy to use database application (few argue that it is not even a database, it is a frontend for an external database server, they say).

So much for the advantages. The disadvantages with an Access database are as:

1). It cant be loaded!!! I mean, its capacity would not go more than a gb as far as I see, it slows down relatively after that.

2). Don’t shoot too many queries. This must be the case with any database per se, but then with Access, it takes ages to retrieve any information in such cases, which again is from a personal experience. To put it in an example, one of my projects had to fetch data from two different tables with a join to relate. However, I failed to understand the nature of joins, and ended up writing 3 different queries to fetch the data. All this is just an 8mb .mdb file. It took a tremendous 90 seconds for us to open our web page, and the query had to be changed immediately to a single one. This does pinpoint at some of the inefficiency in the coding practice, nevertheless taught an important lesson of not to hit an ACCESS database was too often.

3). It needs to be dumped on a server to make it accessible to various people, which in itself is a security threat.

4). No concurrent connections please!!! It could crash if there are too many concurrent connections, thus disabling you from the option of using it for heavy traffic websites. Experts quote a maximum of 3 concurrent connections, thus making it worthwhile for a use as a personal database.

5). Locking up and crashing problems: One cannot open an access database file during the run of the application, as it results in access database locking the information that it possesses. Thus we run into a high risk of rendering corrupted records.

6). Most importantly, no more stored procedures!!! Hows that for a bumpy ride?

In short, be sure before using an access database, its relatively easy for household applications (or that’s the way it could be termed as), but not for serious requirements.