part 1 – part 2 – part 3

This is the third part of a three-part series, have a look at part 1 and part 2 first.

So let’s go on ahead and jump into the final bits of my troubles with MS Access and VBA as environment for medium and larger IT projects – basically, anything having 2 and up users.

Syntax

There are quite a few things that bother me about the VBA syntax. First of all: it’s inconsistent, even more so than the one of php (where mainly method signatures are inconsistent), in several places. The most prominent of them are functions. Take these lines:

Dim answer As VbMsgBoxResult
MsgBox "Hello World!", vbOKOnly
answer = MsgBox("Hello World?", vbYesNo)

Did you notice anything? Yes, indeed. Take note, the parentheses are forbidden in the first case, and mandatory in the second. Now assume that you have a function call that you suddenly need the answer from, whereas you didn’t need it earlier. Not only do you now have to write the assignment, but you also have to add parentheses to the function call. Way to spend productive time.

The same goes for “Set” and assignments to native data types and Objects: For Objects, it’s mandatory, for native types it’s forbidden. That goes even for Objects stored in a Variant variable – all Variants are not made equal, some will throw runtime errors when you assign stuff to them.

Another annoying syntax bit is this one:

' Won't work!
Dim someNumber As Integer = 5
' Will work :-/
Dim someNumber As Integer
someNumber = 5

Yay, more lines of code, how great. But wait, I have more! What do you think this produces?

Dim firstVar, secondVar As Double
Dim thirdVar, fourthVar As Object

Yep, you guessed it. “firstVar” is a Variant, “secondVar” a Double, “thirdVar” a Variant, “fourthVar” an Object. Oh, you got it wrong? Don’t worry, it’s just that silly.

My final pet peeve with the syntax is its capitalization (and thus technically the IDE and not VBA itself). VBA is not per se case sensitive – and it expresses that through simple means: Whenever it encounters a variable, it tries to capitalize it the way it’s supposed to be, to make it clear that it’s the same.

Now, enter scoping, and intuitively named API bits. So you have a local variable called “currentItem” … what happens if you try to access the property “CurrentItem” in, say, an Inspector Object? Right, it’s capitalized “currentItem”. It has nothing at all to do with my local variable, but the IDE is too stupid to notice.

Of course there’s other things amiss or strange, like the lack of a side effect free ternary operator, or the (nearly syntax) VBA developer gospel for the redundant Hungarian notation taken to wrong extremes, but I’ll just stop here.

MS Access

I won’t even go into how I find Access 2007 to be much worse than its predecessors when it comes to usability, with longer mouse paths and more clicks per action, the developer tab not showing by default, and shortcuts no longer easily visible. Just so much: it’s annoying. I guess I still haven’t gotten used to the new, less quick, way of displaying shortcuts, and often, object placements aren’t intuitive for me and I’m looking for things in the wrong tab.

What really bothers though is that Access has stability issues. Access is the only program so far that managed to crash on my development system. Dreamweaver MX 04 is quite an old make and has huge problems with Vista’s UAC, but it doesn’t crash. Nor does any other tool. But Access, which happens to often corrupt databases when it does.

Even crashes aside, occasionally the database just corrupts like that, without any visible reason. Or it keeps code that you deleted, and keeps spitting error messages how that code isn’t valid – without you having any possibility to do anything about it, not even overwriting helps.

I hope you make backups every couple minutes, I certainly learned to. I haven’t found another reliable way to get those databases back into stable conditions.

Then, what’s one of the many responsibilities of a proper DBMS? Housekeeping! What does Access do? Provide you with “compact and repair”, which is user-initiated only. Well, I guess with huge databases that Access couldn’t handle anymore anyway, that would make sense, not having that an automated regular task.

Furthermore, every single user needs MS Access installed on his computer. Potentially a security risk, certainly not something that should be necessary. You don’t go and install an MS SQL Server on everybody’s machine. At least Microsoft consequently follows through, and their Access security FAQ is all about how you can protect your computer from malicious Access macros, and not how you can secure your Access databases.

By the way, Access doesn’t scale – what else is new. Multiple users are a problem, for larger databases (>2GB, but you’re hitting performance bottlenecks way before that) you have to move to a proper backend – essentially, lugging around the whole DBMS part of your GUI windowing toolkit for naught afterwards.

It does at least have proper locking, quite refined permissions, and with Access 2007, database encryption (without which all the permission structures in the world are worth naught) as well.

Other Things

There’s a few other small things that I found incredibly annoying, coming from other languages like Java, Python and my personal favourite, Ruby.

For one, Null comparisons work in odd ways, VBA uses ternary logic (although admittedly, this is consistent with the way SQL handles Nulls).

The next minor thing are silly defaults – there are two lines every halfway serious programmer adds as a very first thing to every single bit of VBA code they write (and this goes so far that there’s option settings to add it automatically to every source bit):

Option Explicit
Option Compare Database

The reason for this is simple: backwards compatibility. While VB6 (maybe earlier?) changed the default to the need for explicit variable declaration, VBA never did, and thus it’s still possible to mistype a simple variable and never find the error later on. And as for Compare Database … yeah sure, let’s compare stuff everywhere a bit differently, to make sure the database behaves completely unforeseeable.

Another one: No external source file support. If you want to use any library or other cool thingy you found on the net or elsewhere, you have to copy-paste the code into Access. Where it’s needed, that is, which of course means that if it’s needed anywhere else, you have to copy-paste it again. The only way around this are Access databases that allow you to copy-paste (oh, look, a pattern) the respective classes or modules as a whole. No, you can’t just link (with very limited exceptions). Hooray?

We’re not done yet: You can’t return an array! If you want a Function that returns an array, you have to define it as returning a Variant. If you do that, you can return arrays (since they’re a subtype of Variant), but will lose all meta information: Data type, size, everything. So if you want to return an array and its size, you have to do it via separate functions, or with a stateful returning object, or with a custom ADT.

Something that was rightfully pointed out to me (thanks Ste!) – why is it that VBA (and VB.NET, for that matter) distinguishes between Functions and Subs? There’s no fundamental difference, one returns something, the other doesn’t (so, essentially returns Nothing), but that’s it. Visual Studio goes so far to automatically change the keyword from Sub to Function when you return something.

Conclusion

I guess it’s apparent by now: I do think that it’s justifiable to say that VBA is outdated and should be dumped right now.

For Microsoft: There are good replacements, there’s no reasonable reason beyond “it was easier” to have Office 2007 use VBA still, and there’s certainly no reason to make the next version of MS Office still have VBA support – particularly if that means that there still won’t be native VB.NET support inside MS Office.

How delusional of me to think they’ll actually see this, though

For anybody considering Access and VBA for their projects: Well, if you still want to, you can of course. Or get the free Visual Studio Express for VB.NET plus SQL Server Express, and stick to a Microsoft environment with a very similar but much more mature language, and a more performant database, for less money.

I still do work with VBA, when I really have to, but I think I laid out here why it’s a fact that I really only do so when there is no other way.

part 1 – part 2 – part 3