in defense of Access

I spend a lot of time using Microsoft Access, which generally inspires ridicule from people who use more powerful databases. They say that Access doesn't scale up to larger databases, can't handle more than a handful of users at a time, doesn't have sufficient database integrity... all of which is true. But Access is fine for many of the projects I have to do. It also has an adequate form designer and report writer, so if I need a simple database, I can put together something that's fairly easy to use, fairly quickly.

On the other end of the spectrum, people who normally stick to Word, Excel and PowerPoint don't much care for Access either. The other day I was talking to someone at work who said she hated Access. To me, that's like saying "I hate this hammer" because I dropped it on my foot. You need to learn how to use it, and when to use something else. Granted, Access has a slightly steeper learning curve. OK, maybe using Access is like baking a pie, I dunno. Work with me, people.

I also work with Microsoft SQL at work, as that's what powers our clients' databases. (There's a learning curve for you - it's like trying to figure out the controls of a Russian nuclear submarine.) MS SQL has a feature called DTS (Data Transformation Services) that we use to export data from these databases to various Web sites. You design a query to select the data you need, then you export it and ftp it to the Web site.

When I put together a query for a DTS package, I often build the query in Access first, since I'm more comfortable with Access. Then I have to translate the query into SQL, since Access uses some SQL commands, but the syntax is usually different. And Access has some functions that don't directly translate to SQL. Once I translate the Access query into proper SQL, I can compare the results to make sure I've done it right.

You can imagine my surprise when I discovered an Access query that executed faster than its SQL equivalent.

The difference seems to be an Access function that doesn't have an exact equivalent in MS SQL. In this particular case, there's a field called 'product_code' which may contain a space (such as ABCD EFGH): if it does, I want to ignore the stuff that comes after the space (so the query should return ABCD). In Access, I used the InStr function. If there's a space in the product_code, make a note of where that space is, then truncate the product code at that space:

Product_Code2: IIf(InStr(1,[Product_code]," "),Left([Product_Code],InStr(1,[Product_Code]," ")),[product_code])

For example, given ABCD EFGH, the space is the fifth character. The 'Left' function will then take the first 5 characters of the product code. Even as I type this, I realize that I screwed up - I should be taking the first 4 characters, not 5. But you get the idea.

In MS SQL, I used the CHARINDEX function. The end result is the same, but CHARINDEX apparently is slightly slower at finding the space.

Product_Code2 = Case When CHARINDEX(' ',a.PRODUCT_CODE) > 0 then LEFT(a.PRODUCT_CODE,(CHARINDEX(' ',a.PRODUCT_CODE))) else a.PRODUCT_CODE

Somehow, Access is faster at finding the space in that string of text.

I called over my friend Scott, one of those people who generally looks down upon Access. He stared at the code for a while, then walked away wordlessly. But I didn't crow about it - after all, I work in IT, which essentially means that I am always wrong. If the Access query is faster, that just means that I didn't do a good job designing the SQL query.