This article is more than 1 year old
Migrating Access to SQL made (almost) easy
Very useful, apart from the dumb bits
Access dominates the PC platform and, over the years, has been used to create vast numbers of departmental databases. In their turn, many of these have slowly become mission critical and now need to be upgraded to a secure client-server engine.
In Microsoft's grand plan for world domination, it would prefer that engine to be SQL Server and, with that in mind, has provided, free of charge, SQL Server Migration Assistant for Access (SSMA) that does what it says on the can and you can download it here
For developers, migration tools can be a huge boon. It is unrealistic to expect this tool to migrate an entire application because Access has facilities (such as Form and Report capabilities) that simply don't exist in SQL Server. However it is reasonable to expect it to do a huge chunk of the grunt work – creating the appropriate tables, moving the data, turning queries into views and so on.
As well as XP or 2003, SSMA requires the .NET Framework version 2.0 or later, the J# 2.0 redistributable package and at least 1 GB RAM.
It has a fairly minimalist GUI, essentially just four panels. After creating a new project you add one (or more) Access databases and then connect to the appropriate SQL Server database. Next step is to convert the schema to SQL Server.
Note that this process does not run the schema against the SQL Server engine; it simply produces a potential SQL Server schema which is visible in SSMA. It also produces a set of error, warning, and information flags.
At this point, the power of the tool becomes apparent. For a start the flags point you to conversion problems, for example, the fact that Access functions like DateDiff are not supported and cannot be converted (they can, of course, be converted, but not by SSMA).
You can also browse the Access schema, look at the type mapping the Assistant is proposing, and so on. In addition, if you don't like the default mapping, you can globally change the default and/or change them for a particular project or even a particular table.
Queries are a somewhat special case. They are converted into SQL Server Views: you can edit the Access query and then regenerate the appropriate SQL Server code. This editing occurs in the schema held by SSMA, not in the Access database itself.
When you are happy with the schemas you can use SSMA to run the SQL Server schema against the database. That creates the structure to hold the data so that you can then migrate the data. In theory it all sounds wonderful, but what is it like in practice? Well, trying to migrate from any database engine to another is painful and this tool will do 90 per cent of the work for you. It's also free, so it has to be great. That said, some of the bugs are mind-blowing.
For example, although it isn't part of the SQL Standard, Access requires all dates to be wrapped up in hash symbols. Sadly, no one appears to have told SSMA. The net result of that failure is that all my queries that reference dates failed to convert. A sample error message looks like this:
/* * SSMA error messages: * A2SS0058: Following SQL statement is not supported and cannot be converted: * * SELECT DISTINCTROW EMPLOYEES.EmployeeNo, EMPLOYEES.FirstName, EMPLOYEES.LastName, EMPLOYEES.DateOfBirth, EMPLOYEES.DateEmployed * FROM EMPLOYEES * WHERE ((EMPLOYEES.DateOfBirth)>#1/1/1970#); * */
PRINT 'ERROR: SSMA failed to convert the previous statement.'
Now, dates are common in databases so this failure is going to impact most database conversions. How was this overlooked? It isn't as if the solution is difficult:
SELECT EmployeeNo, FirstName, LastName, DateOfBirth FROM dbo.EMPLOYEES WHERE (DateOfBirth > CONVERT(DATETIME, '1970-01-01'))
returns the correct set of data from the sample.
(We can argue about whether, for example:
CONVERT(DATETIME, '1970-01-01 00:00:00', 102)
might be more appropriate but, either way, we can convert the date handling.) And if we can do it by hand, SSMA should be doing it for us.
And it gets worse (no, really). Access, by default, uses double quotes around text, for example:
SELECT EMPLOYEES.EmployeeNo, EMPLOYEES.FirstName FROM EMPLOYEES WHERE ((EMPLOYEES.FirstName="Norma"));
SQL Server doesn't, it demands single quotes, as in:
Nevertheless, SSMA leaves the above double-quoted code exactly as it is. This fails to trigger an alert during the schema generation; the alert only occurs during the process of loading the schema to the SQL Server database. Then SSMA throws up an error saying there is an invalid column name Norma and the View is not loaded into SQL Server. All of which tells us that SSMA is not performing an adequate syntax check on its own code.
Now, let me stress again, Access uses double quotes BY DEFAULT. The fact that the SSMA fails to deal with this incredibly simple and common Access syntax leaves me gob-smacked. It's as if a human French-English translator could process the majority of the language but was flummoxed by the word "Bonjour".
One more example. Access allows you to add a validation rule to a column such that, for example, the only acceptable values in a field called "Title" might be Mr., Mrs., Miss., Ms, etc. SQL Server doesn't support exactly the same type of validation so, very sensibly, SSMA converts the validation rule into a table constraint. Brilliant, well done. Only it misses out the name of the column in the code it writes:
ALTER TABLE [dbo].[NAMES] ADD CONSTRAINT [SSMA_CC$NAMES$Title$validation_rule] CHECK (In ('Mr.','Mrs.','Miss','Ms','Dr.','Prof.'))
This not only fails to run when the schema is loaded into SQL Server, it actually fails to generate an error message as well. The correct syntax for the last line should be:
CHECK (Title In ('Mr.','Mrs.','Miss','Ms','Dr.','Prof.'))
So, am I going to remove SSMA from my machine? Of course not. It does do a huge amount of work for me entirely automatically. It also provides a reasonable environment where I can see the problem areas and fix them. In addition, complaining about a free tool feels like looking a gift horse in the mouth. Despite all of that, I can't help wishing that SSMA had better teeth.
The reason we need conversion tools like SSMA is partially based on the fact that dialectic differences exist between different SQL implementations. Some of these differences are very esoteric and will impact a very small number of real-world queries. Others (such as hash symbols and double quotes) are widely used in most Access applications. What is so surprising about SSMA is that it is the common-place dialectic differences that it fails to convert. What were the developers of SSMA for Access thinking about? ®