Migrating Access Apps to SQL Server

September 21, 2007

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers