Converting Access Queries to SQL Server

In last month's article, I introduced a Microsoft Access Add-In I created some years ago, which I affectionately call the DataFast Utility. It includes a number of analysis tools that assist with the process of upsizing an Access application to SQL Server. If all of your queries are simple, the utility can even create stored procedures and push them up to your database. However, odds are your queries are not that simple and they're going to need a little tweaking before they will meet T-SQL syntax standards.

This month's article will focus on a few of the more common conversion scenarios you will face. While this is going to look a lot like a T-SQL article, it is designed to help developers who are accustomed to the VBA environment of Access to make the transition to the world of SQL Server script. Here are the conversion topics we will address:

One last point that isn't grand enough to be a topic, but deserves mention is the DISTINCTROW keyword. This is an Access-specific SQL keyword that doesn't exist in SQL Server. I've read the help topic and hashed out the details with my Access developer friends, but I still can't remember the difference between DISTINCTROW and DISTINCT. Suffice it to say, that you will need to convert any reference of DISTINCTROW to DISTINCT if you want it to run in SQL Server. With that out of the way, let's get on to the fun stuff.

Replacements for VBA functions; Date() and Now()

Date functions should be really easy, but I have to admit that it's my biggest T-SQL disappointment. Maybe it's because I come from the world of VBA, with simple, logical date functions and intelliSense (thank you Red-Gate for SQL Prompt and Microsoft for SQL Server 2008!) Truth is, working with dates in SQL Server is challenging for Access developers. Below is a 'cheat sheet' for some common date methods that will hopefully ease the pain a bit.

Date() and Now()
Simply stated, you use the GETDATE() function in T-SQL to return what you get with the Now() method in VBA. That includes the TIME portion of the date. To get just the date portion (Date() in VBA is not so simple. You need to call GETDATE() and convert it to another format.

You can look this up the syntax in SQL Server Books Online, but basically, the Help system sucks. After years of reading how to use the CONVERT() function, I only finally figured it out when a fellow developer explained the 'trick' to me. I had always thought that the CONVERT function (see image below) required a DateTime datatype in connection with the date style argument:

    CONVERT(DateTime, GETDATE(), 101)

The last argument, 101 in my example above, determines the date format. (See MSDN for a full explanation of the CONVERT function.) However, this is entirely wrong. In order for any of the Styles to be applied, you must convert the date to a string, like this:

    CONVERT(VARCHAR(10), GETDATE(), 101)

This format will give you the results shown below. This is the T-SQL, poor-excuse-of-an-equivalent for the awesome-and-flexible Format() function in VBA. Yes, VBA, the ugly step-child of development has once again surpassed the robust, grown-up development languages. Maybe SQL Server 2020 will finally provide the power and simplicity of what Access developers have enjoyed for nearly two decades.

Am I being too harsh? Hmmm. Notice the last results column, in the screen shot below. The TIME portion returns 6:56AM. What if you wanted a space in front of the AM? Sure, it can be done in T-SQL, but not without some pretty verbose string manipulation calls. VBA Rules!

For a full list of T-SQL date and time functions, see this MSDN article:
    Date and Time Functions (Transact-SQL)

T-SQL equivalents for InStr() and Mid()

I often use other VBA functions in my Access queries, especially Trim(), Left(), InStr() and Mid(). The LEFT() function in T-SQL works the same as that for VBA, and the Trim() function requires only a simple adjustment. T-SQL, not being as intelligent or flexible as VBA, requires that you trim from both sides. There is no TRIM() function, but you can do a Left Trim and Right Trim combination that approximates the Trim() of VBA:

    SELECT LTRIM(RTRIM(' Now is the time ')) AS TrimResults

The T-SQL equivalent of InStr() is the CHARINDEX() function. The syntax for it's arguments is flip-flopped from the VBA version. The first argument should be the string you are looking for, while the second is the string to be interrogated. The last argument is the starting location. This takes some getting used to, but it works pretty much the same way as its VBA counterpart:

    SELECT CHARINDEX('is the', ' Now is the time ', 0) AS InStrResults

The VBA function to extract characters out of the middle of a string, the Mid() function, is accomplished in T-SQL with the SUBSTRING() function. If you are familiar with the Mid() function, you won't find the syntax confusing. It works the same way, as you can see from this example.

    SELECT SUBSTRING(' Now is the time ', 6, 6) AS MidResults

Below are the results as shown when run from SQL Server Management Studio.

How to convert the IIF() function to T-SQL

The IIF() function in VBA is also called the Conditional If because it evaluates a single condition and then branches accordingly. It might look like this ...

    IIF([ytd_sales] > 4000, "Great!", "Ok")

The T-SQL replacement for this would be a CASE statement. The full description is available on MSDN in the following article: CASE (Transact-SQL). The screen shot below shows how it might be applied to the titles table of the Pubs database. Once you get used to the syntax, you'll find the CASE statement of T-SQL to be more powerful and flexible than nested IIF() statements, which can get really confusing to edit.

Yes, that's right! I said something nice about T-SQL. Yes, it actually out-shines VBA in this instance. Even a broken clock is right twice a day.

Merciless T-SQL string concatenation explained

Ok, we're not going to harp about this one for a long time, but just enough to prepare you for what is to come, as you delve into T-SQL a bit deeper. In Access VBA, you can concatenate all kind of things into a single string. It wasn't unusual for me to do something like this in a query ...

    ResultMsg:  "You have " & [Order Count] & " orders ready to ship on " & [Ship Date] & "."

Yeah, forget about doing that in T-SQL. Just forget it! Don't try it. It doesn't work. To concatenate strings in T-SQL, every piece of it must be a string. Plus, you don't 'ampersand' them together ... you 'add' them together, with the PLUS operator, like this ...

    SELECT 'You have ' + CONVERT(VARCHAR(8), [Order Count]) + 
           ' orders ready to ship on ' + 
		   CONVERT(VARCHAR(10), [Ship Date], 1) + '.'
        You have 23 orders ready to ship on 10/10/07. 

You should also note the use of the single quote, instead of a double quote. You'll get used to this too. However, on another positive note, you can wrap the SELECT statement to the next line without a line continuation character. T-SQL scores again!

Stored Procedure verses View dilemma

This topic is simpler yet. If you are converting an Access MDB to link to SQL Server tables, views and procs, you really only have a couple of choices. Stored Procedures are not updatable in an MDB. (If you have an Access Data Project, you can set a stored proc as the updatable recordsource of a form.) So, you use stored procs for the rowsources of combo and list boxes, for read-only forms, and for reports. If you need to update the data, use the table, or link to a view.

To improve performance on the read-only datasets, you should investigate the use of Pass-Through queries. See my article in the archives, How to Execute SQL Stored Procedures from Microsoft Access.

Suggestions for advanced issues: T-SQL Functions

The last category has to do with custom functions you may have created in Access VBA. Perhaps you have a call that performs a calculation or one that looks up the sales rep name. These will have to be painstakingly reproduced as T-SQL functions. The good news is, once you migrate the logic, probably using the techniques above, you can implement your function in pretty much the same way it's done within Access.

For example, let's say we wanted to encapsulate the logic shown above

    CREATE FUNCTION fnc_GetSalesStatus (@SalesAmount MONEY) 
        RETURNS NVARCHAR(8) 
    AS
    BEGIN
        DECLARE @ResultMsg NVARCHAR(8)

        SELECT @ResultMsg =
            CASE 
              WHEN @SalesAmount 7lt; 1000 THEN 'poor'
              WHEN @SalesAmount BETWEEN  1000 AND 4000 THEN 'Ok'
              WHEN @SalesAmount > 4000 THEN 'GREAT!'
              ELSE 'unknown'
           END
        RETURN @ResultMsg
    END

Once created, you simply call the function from your SELECT statement, like this ...

    SELECT title, dbo.fnc_GetSalesStatus(ytd_sales) As Sales_Status FROM title

You must remember to prefix the function call with the function owner, which defaults to DBO if an owner is not specified when creating it.

This is, of course, just a single example, and an oversimplification of the process. Migrating the logic of your functions to T-SQL is not trivial, but it basically follows the above template. If you take the time to migrate your logic, you will likely be rewarded with a performance boost. It's nearly always going to be faster to allow SQL Server to process the data, than to cobble it in the front end, especially with complex functions that require database lookups.

Conclusion

Whew! That was a lot and honestly, there are still more topics that could be considered in depth when it comes to converting Access queries to T-SQL. I hope that the examples above are sufficient to get you started. Writing code in SQL Server can be fun, once you get the hang of it.

» See All Articles by Columnist Danny J. Lesandrini