Feeds

Migrating Access to SQL made (almost) easy

Very useful, apart from the dumb bits

  • alert
  • submit to reddit

Internet Security Threat Report 2014

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.

The simple GUI for SSMA

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:

WHERE EMPLOYEES.FirstName='Norma';

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? ®

Top 5 reasons to deploy VMware with Tegile

More from The Register

next story
Download alert: Nearly ALL top 100 Android, iOS paid apps hacked
Attack of the Clones? Yeah, but much, much scarier – report
You stupid BRICK! PCs running Avast AV can't handle Windows fixes
Fix issued, fingers pointed, forums in flames
Microsoft: Your Linux Docker containers are now OURS to command
New tool lets admins wrangle Linux apps from Windows
Facebook, working on Facebook at Work, works on Facebook. At Work
You don't want your cat or drunk pics at the office
Soz, web devs: Google snatches its Wallet off the table
Killing off web service in 3 months... but app-happy bonkers are fine
First in line to order a Nexus 6? AT&T has a BRICK for you
Black Screen of Death plagues early Google-mobe batch
prev story

Whitepapers

Choosing cloud Backup services
Demystify how you can address your data protection needs in your small- to medium-sized business and select the best online backup service to meet your needs.
A strategic approach to identity relationship management
ForgeRock commissioned Forrester to evaluate companies’ IAM practices and requirements when it comes to customer-facing scenarios versus employee-facing ones.
Go beyond APM with real-time IT operations analytics
How IT operations teams can harness the wealth of wire data already flowing through their environment for real-time operational intelligence.
The total economic impact of Druva inSync
Examining the ROI enterprises may realize by implementing inSync, as they look to improve backup and recovery of endpoint data in a cost-effective manner.
Reg Reader Research: SaaS based Email and Office Productivity Tools
Read this Reg reader report which provides advice and guidance for SMBs towards the use of SaaS based email and Office productivity tools.