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:
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:
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.