Migrating MS Access based application to SQL Server 2008

Question

I'm currently working as an intern in a medium sized company. My main job was to work on a Java application that relied strongly on MS Access databases with some 150 tables and 200 queries. Now that I have finished that I was told it would be nice to move from that Access database to SQL Server 2008 that the company has a license to.

I have already attempted doing this and only learned how difficult my task is. But I kept going and so far I managed to:

• discovered SQL Server Migration Assistant for Access as the best tool (available from MS)
• I have gathered all the dbs into a single file
• with the help of SQL SMAA reports I have updated all the tables with keys, and updated most of them to be non nullable
• I am now able to move all the tables with no issues (that I know of)
• done some tests and managed to convert manually about 50 of the queries, with another 30 that SMAA is able convert to Views

Now about 10% of all queries I reckon will be really difficult for me to convert and some of them may end up having repeated code just to avoid the incompatibility. The problem is the way they are designed (a lot of them take multiple table columns, join them with columns taken from SELECT queries... and then are used within another query where the same repeats - creating messy and difficult to convert chains) and another problem is that I had no previous experience with databases and finding resources to help me in my struggle is near impossible. No books about SQL help as I think my problems are too specific.

So I'm addressing you to help me find best course of action. I thought of these possible scenarios:

• I go on, possibly creating some monstrosities of SQL functions and procedures, and it probably will WERK
• Explain to my supervisor that it is not feasible to continue with the full migration. Suggest moving the data to the Server and leave the queries alone, so that they work with SQL tables instead. (Is it wise though? My supervisor hoped for more reliable back-end, robust and with any performance gains as a an extra)
• ???

I should add that I have about 4-5 months for that, after which my internship ends. I will have to worry about getting the application to work afterwards as well. There are some other small projects that were suggested to me, that I could do instead (and I would probably be more interested doing), this one was prioritised though.

Answer 1

You may be over-thinking this into something fearful. In MS Access, a SELECT Query is (functionally) the same as a SQL Server View. Transforming such Queries into Views should be a straight-forward job.

(I suppose it should be mentioned that an Access Query allows for parameters to be embedded within a query, whereas an SQL Server View does not. However, since this is about a Java application accessing Access, I am guessing this won't apply. Let me know if otherwise.)

UPDATE and INSERT Queries may need to be parsed out so that the SELECT portion is replaced with a View under SQL Server, if these be among the 10% you speak of. But that shouldn't be too challenging either.

If anything, VBA code and Macros would have been where the more difficult things may lurk, but it seems you've lucked out there.

So, I would suggest you proceed in making them into Views, and at least make this into a learning opportunity about SQL Server. I believe once you start doing this, you'll figure out how to sort out these tough-to-crack nuts.