Sunday, February 25, 2007

Poor Quality in action: Access

Today I wish to draw your attention to another example of poor quality in action. As with the last one, the culprit is.... Microsoft.

Microsoft Access 2000, to be specific. It has another of those wonderful useability features that turns your brain to mush after ten minutes use.

Now, there's an argument to be made that Microsoft Office suites are primarily designed for the use of retarded monkeys, and hence we shouldn't expect them to be developer-friendly by default. That's fair enough - I can quite understand that a product like Access may have to ship with all features enabled (although, as I've declaimed at great length, an option to turn the features off would be greatly appreciated). However, the precise implementation of this particular feature makes the aforementioned retarded monkeys look like Deep Blue on steroids.

The feature in question is the little gizmo that reformats your SQL* syntax for you, which as best I can tell is intended to make your code more readable to the machine. However, Access isn't content to reformat the code for its own use. Oh no, Access is too friendly for that. Access shares.

The practical upshot is that my beautifully-laid-out code keeps getting turned into a reader's nightmare. The following is an example - a bit of code that grabs some asset-related data in an attractive fashion. Before:

SELECT    CPN.Asset_Tag,
dAC1.Asset_Category AS Record_Category,
dAC2.Asset_Category AS Review_Category,
dAT1.Type AS Record_Type,
dAT2.Type AS Review_Type,
dAM1.Name AS Record_Manufacturer,
dAM2.Name AS Review_Manufacturer,
CPN.Record_Model,
CPN.Review_Model

FROM (
CompareProductNum AS CPN
LEFT JOIN dbo_Asset_Types AS dAT1
ON (
(CPN.Record_Category_ID = dAT1.Asset_Category_ID)
AND
(CPN.Record_Type_ID = dAT1.ID)
)
LEFT JOIN dbo_Asset_Types AS dAT2
ON (
(CPN.Review_Category_ID = dAT2.Asset_Category_ID)
AND
(CPN.Review_Type_ID = dAT2.ID)
)
LEFT JOIN dbo_Companies AS dAM1
ON CPN.Record_Manufacturer_ID = dAM1.ID
LEFT JOIN dbo_Companies AS dAM2
ON CPN.Review_Manufacturer_ID = dAM2.ID
LEFT JOIN dbo_Asset_Category AS dAC1
ON CPN.Record_Category_ID = dAC1.ID
LEFT JOIN dbo_Asset_Category AS dAC2
ON CPN.Review_Category_ID = dAC2.ID
);


And after:

SELECT CPN.Asset_Tag, dAC1.Asset_Category AS Record_Category, dAC2.Asset_Category AS Review_Category, dAT1.Type AS Record_Type, dAT2.Type AS Review_Type, dAM1.Name AS Record_Manufacturer, dAM2.Name AS Review_Manufacturer, CPN.Record_Model, CPN.Review_Model

FROM (((((CompareProductNum AS CPN LEFT JOIN dbo_Asset_Types AS dAT1 ON (CPN.Record_Category_ID=dAT1.Asset_Category_ID) AND (CPN.Record_Type_ID=dAT1.ID)) LEFT JOIN dbo_Asset_Types AS dAT2 ON (CPN.Review_Category_ID=dAT2.Asset_Category_ID) AND (CPN.Review_Type_ID=dAT2.ID)) LEFT JOIN dbo_Companies AS dAM1 ON CPN.Record_Manufacturer_ID=dAM1.ID) LEFT JOIN dbo_Companies AS dAM2 ON CPN.Review_Manufacturer_ID=dAM2.ID) LEFT JOIN dbo_Asset_Category AS dAC1 ON CPN.Record_Category_ID=dAC1.ID) LEFT JOIN dbo_Asset_Category AS dAC2 ON CPN.Review_Category_ID=dAC2.ID;


Now, even the less computer-literate amongst my readers (both of them) should be able to recognise a slight readability difference between version 1 and version 2 of this code. The difference is: you can't read version 2. Every time I stick any SQL code into Access, this is the sort of gobbledygook that Microsoft, in its infinite wisdom, transforms it into. Pity me.

And can you turn it off? Don't be silly, says Microsoft, why on Earth would you want to do that?

----

* Structured Query Language, a sorta-kinda programming language that's used for getting information out of databases
Read the full post