T-SQL Equivalents for Microsoft Access VBA Functions
By: Danny Lesandrini
I was introduced to SQL Server version 7.0 a decade ago when I was asked to convert all of the queries in a shrink-wrapped, production ready Access application that managed room reservations. I had never written a line of T-SQL, never built a stored procedure or even a view for that matter but I wasn't worried. I mean, how difficult can it be? It's all SQL language, isn't it?
Actually no, it isn't. Microsoft Access allows users to embed VBA functions in the SQL query language. From things as simple as Date() to things more complicated like the IIf() function. Access also allows you to leverage your own VBA functions right from the query, like custom functions to perform calculations or validate phone and email strings.
Doesn't the SQL Server Upsize Wizard in Microsoft Access automatically convert these queries? No, sadly, it doesn't even try. If you want to push the complex query processing done by your Access queries to the back end, you'll have to rewrite them in T-SQL. If that's a task you have in front of you, then the cheat-sheet below is really, really going to help.
High Level Overview
Without beating the details to death, here is a quick reference guide to the T-SQL commands that map to the Access functions we are most familiar with. If unsure what the options are for the T-SQL functions, simply type the function keyword and press F1 to bring up Books Online help.
Watch Out for These Gotcha's
There are a couple of tricks to watch out for. First, the function signatures sometimes match the VBA ones exactly, but don't expect that to be the case every time. If you don't get the results you expect, review the help file again to make sure you're passing arguments in the correct order.
Second, T-SQL is much less forgiving than Access. In T-SQL one uses the plus operator (+) to do both concatenation and addition. Numbers are automatically added together. Text strings are automatically concatenated. But if you try to "plus" the string "the order number is " with the integer value 2345345, you'll get an error. Numbers, currency and date values must be converted to string values to be concatenated to other strings, like this ...
Other Related Articles
... to read more DBA articles, visit http://dba.fyicenter.com/article/