Migrating Access Apps to SQL Server

Analyzing and Migrating Queries

Back in 2000 (remember Y2K?),
Marc Israel started a series of Access articles on the theme: Migrate to SQL
Server. Marc suggested several titles for the series but some of them were
never written and recently, readers have been asking if those topics would ever
be addressed. Migrating
Access Applications to SQL Server by Marc Israel

Over the next few months, I’m going to take a stab at some of them. This month,
Queries. Next month, query optimization and after that, if the spirit moves me,
some tips and tricks to make table migration proceed more smoothly. These
articles will not explain how to use the SQL Server Upsize Wizard. There’s
plenty of information on that topic available through technet at the Microsoft
site. For example, see the following link:
    How
to Migrate from Access to SQL Server 2000

This, and subsequent articles, will focus on some of the subtleties of
migration. We’ll introduce some home-made tools that may be useful to your
migration process, and explain a few of the "gotcha’s" associated
with the move from a Microsoft Access database to SQL Server 200x.

Download the DataFast Utility

This month’s download is what I affectionately call the DataFast
Utility
.

It represents the work product of a complex Access to SQL Server migration I
did back in 1999. Sure, the Upsize Wizard migrated the tables, but for the queries,
I was on my own. Accordingly, I built a number of diagnostic tools to document,
analyze and morph my queries to make the manual conversion from Access query to
SQL Server Stored Procedure less painful. However, before we see how these
tools work, a word about how to load the Add-In.

If you’re familiar with how Add-Ins work in Microsoft Access, simply download
the zip file from the link above and select the correct Add-In version for your
Access installation. It includes an Access 97 version, and an Access 2000 version,
which works with A2K to Access 2003. If you’re new to Add-Ins, consider the
brief, tutorial of screen shots below:

1. 
Select Add-In Manager from the Tools | Add-Ins menu and click the Add
New button (not shown).

2. 
Navigate to the folder where you’ve saved the DataFast Utility file,
DFUtility2k.mda. I always save my mda Add-In files to C:\Program
Files\Microsoft Office\Office11\ for my Access 2003 install.

3. 
After clicking the Install button, an X appears next to the Add-In name
and the Install button changes to Uninstall. Click the Close button … we’re
done here.

4. 
A new option, DataFast Utility, shows up on the list of Add-Ins. Click
it to launch the utility.

5. 
Voilà! You may now use the utilities I created for my SQL Server
migration in your application, and it will be available to every Access
database you open from here out, until you uninstall it from the Add-In Manager
dialog.

Assuming you’ve got the utility installed, we can move on to
using some of its gadgets. Some apply specifically to upsizing to SQL Server,
while others, like the Query Timing device, are just nice things to have
around. Poke around as you like. They are free (and unsupported) tools, but you
may find them useful.

Analyze Your Queries

If you’re like me, your Access applications end up with a
multitude of queries, some of which you have long forgotten why you created
them or where they are used. Sure, you attempted to follow a clever, fool-proof
naming convention, but in the end, chaos always prevails. So, the first things
you need to analyze include 1) Query Metadata, 2) Queries with Sub-queries and
3) Where queries are referenced in forms and controls. The above-selected
option of the DataFast Utility will loop through your queries and forms,
generating the analysis and presenting you with this dialog:

The first analysis is the query metadata: data about the data. For example,
what type of query is it? A SELECT query? An APPEND query? This is very
important to know for the conversion process. The text of the query is also
shown, and some simple analysis is done to indicate the following:

  • Does it contain the IIF statement?
      (this will be converted to CASE statement in T-SQL)
  • Does it use any custom, user-created functions?
  • Does it include a NOT qualifier?
      (could require performance tweaking)
  • Does it use the DISTINCT keyword?
  • Is the Date() or Now() function referenced?
      (convert to GETDATE())
  • Is it referenced in a Combo Box or List Box?
      (means it can be read-only)
  • Is it a UNION query?
  • Ignore the Count column. It always returns 0 and I’m not sure why
    I included it in the code.

These are important things to know and they will help you identify potential
performance issues. We’ll talk more about that in a future article, but for now
it’s good to know how to identify these issues.

The next analysis lists the queries by name, showing where they are referenced
in other queries. Unlike the analysis above, which shows all SQL Statements,
whether named or not, this piece of the analysis looks only at named queries.
In other words, which queries are embedded in other queries. This will be
important as you convert the queries because you will either need to create SQL
Server Views in the correct order (embedded views first) or grab the SQL of the
embedded query and insert it as a subquery in new SQL View or Proc.

This isn’t simple to illustrate, but let me give it a shot. Suppose you had a
query to return orders summary for active sales reps, with rep summary data
included. It could be the combination of two queries. The first with the sales
rep summary data:

    SELECT RepID, RepName, Count(AssignedState) As StateCount
    FROM tblSalesRep INNER JOIN tblAssignedState
    ON   tblAssignedState.RepID = tblSalesRep.RepID
    GROUP BY tblSalesRep.RepID, tblSalesRep.RepName
    ORDER BY tblSalesRep.RepName ASC

Let’s assume that this query is named qrySalesRepSummary in
our Access database. It is referenced in our next query, which shows a daily
order summary count for each rep. We’ll name it qryRepDailyOrderSummary and it
looks like this …

    SELECT OrderDate, RepName, StateCount, Count(OrderID) As OrderCount
    FROM tblOrder INNER JOIN qrySalesRepSummary 
    ON   qrySalesRepSummary.RepID = tblOrder.RepID
    GROUP BY OrderDate, RepName, StateCount
    ORDER BY RepName, OrderDate

Converting this to SQL Server, you could create one VIEW named qrySalesRepSummary
and another VIEW named qryRepDailyOrderSummary without changing a thing.
Alternatively, you could combine them into a single SQL Server view, like this

    SELECT OrderDate, RepName, StateCount, Count(OrderID) As OrderCount
    FROM tblOrder INNER JOIN 
       (
        SELECT RepID, RepName, Count(AssignedState) As StateCount
        FROM tblSalesRep INNER JOIN tblAssignedState
        ON   tblAssignedState.RepID = tblSalesRep.RepID
        GROUP BY tblSalesRep.RepID, tblSalesRep.RepName 
       ) AS qrySalesRepSummary
    ON   qrySalesRepSummary.RepID = tblOrder.RepID
    GROUP BY OrderDate, RepName, StateCount
    ORDER BY RepName, OrderDate

Now, this isn’t SQL Server rocket science and frankly, it will work in
Microsoft Access too. It’s just that creating queries in Access is so darn
simple, we tend to create the queries everywhere and stack them one on top of
another without concern for the number of objects we create, nor for the
performance issues involved.

This isn’t something that can be taught in a simple article, but it’s a start.
Once you can see your queries, you can begin to think of how they might be
rewritten in a more simple way.

The last option for Query Metadata Analysis shows where named queries or raw
SQL is referenced in forms and controls. This analysis will help you to update
the forms and controls in the event that you change query names or convert the
embedded SQL into VIEWS or PROCS. This process also reveals VALUE LISTS as the rowsource
for controls, such as when you list YES;NO options, or a fixed list of numbers:
1;2;3;4;5.

That’s it for the Query Metadata Analysis

Create Stored Proc Scripts

Another option on the DataFast Utility is one that can
convert queries to stored procs. This may or may not be the right thing for
your query, since in an MDB, stored proc calls are read only. However, it does
work well for the rowsource of combo boxes and list boxes. Now it’s clear why
the above analysis is important. Create VIEWS for form recordsources where you
need to update, and STORED PROCS for read only recordsets. Here’s an example
from the database on which I ran the utility. It was a query named qry_frmContacts
and the auto-generated script of the DataFast Utility converts it into a STORED
PROC with the prefix of usp_.

    CREATE PROCEDURE usp_qry_frmContacts

    AS
    SET NOCOUNT ON

    SELECT
        Contacts.*
      , Organizations.OrganizationName
    FROM
       Contacts 
    LEFT JOIN
       Organizations 
    ON
       Contacts.OrganizationID = Organizations.OrganizationID
    WHERE
       (((Contacts.ContactTypeID)<>3 
    OR
       (Contacts.ContactTypeID) Is Null))
    ORDER BY
       Contacts.DisplayName;

Included in the utility is the ability to "push" these procs up to
SQL Server. While this works, it takes away some of the hands-on aspect of the
conversion. If you simply run it, attempt to upsize all of your queries as procs
and report back by checking the column, [PushToServerCausedError], setting it
TRUE where a failure took place. It’s not perfect, but it will identify the
problem objects for you, simplifying your manual process somewhat.

The more complex your queries, the more often the [PushToServerCausedError]
checkbox gets flagged. This is where all those metadata attributes described at
the beginning rear their ugly heads. SQL Server doesn’t like DISTINCTROW,
though it’s easy enough to convert those to DISTINCT. It has it’s own date function,
so Date() and Now() must be converted to GETDATE(). SQL Server has no clue what
is meant by INSTR(), MID() or IIF(), but if you know which queries reference
them, you can rewrite them with CHARINDEX, SUBSTRING and CASE statements. The
more of these things you have in your queries, the better you will eventually
get at writing T-SQL code. Then there are the advanced Access developers who
create their own custom functions and embed them in queries. For that, you’re
on your own. Better get a book on SQL Server and T-SQL. Read up on user defined
functions and you’ll be fine. Heck, you might even decide to abandon Microsoft
Access and become a T-SQL guru.

Article Post Mortem

Did any of this help? As I reflect on what has been
described, I almost doubt it. There is no plug-and-play solution for upsizing
Access queries. If there were, it would have been included in the upsize
wizard. No one can create a utility to convert your years of hard work writing
queries into SQL Server with the click of a button. It’s gonna take analysis,
meditation, and a whole lot of typing. Maybe you are accustomed to using the
Query-By-Example grid in Access, but if it’s your ambition to upsize your app
to SQL Server, get familiar with SQL Server Management Studio, and what used to
be called Query Analyzer, because you’re going to be writing a lot of SQL. But
before you start, download the DataFast Utility and give it a whirl. It just
might simplify your analysis, aid your meditation, and save you some typing.

»


See All Articles by Columnist
Danny J. Lesandrini



Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Latest Articles