MS Access 2007 queries does't run on SQL Server 2008

Question

I am developing an application in C# VS 2010 past 4 months. I used MS Access 2007 to store my nearly 20 tables successfully.

Today I realized that my data base cannot be handled consistently by MS Access 2007. Hence I decided to go for SQL Server 2008 R2 Express with Upsizing wizard and it worked really great!

However, when I tried to run various options of my already well developed application, It kept throwing error each time when a query is fired to SQL Server.

I understood that Many of the stuffs of SQL supported by MS Access are not supported by MS SQL Server

For example: query with date, for representing date format when we use '#', SQL Server 2008 won' t recognize it.

Also, for Bool value, MS Access stores it as True and False where as SQL Server uses 0.

These all queries worked perfect with Access 07

I am sure that there must be some method so that SQL Server can understand MS access queries.

Or will I have to edit my whole application?? It will be as good as digging a mine for earning gold..

I have changed all data access objects such as reader, adapter, command, connection to SQL data objects using System.Data.SqlClient.

So, It is not the problem.

Please help me asap.

Thank you.

Answer 1

You cannot force SQL Server to run the MS Access queries. These queries will need to be rewritten to use T-SQL instead of the query language that MS Access uses.

I feel your pain, I just had to rewrite a large MS Access application (over 1k queries) that needed to be recreated to be used in SQL Server.

There will be some queries that might be able to be ported over directly but as you noticed queries with date, and even some of the aggregate functions (First(), etc) are not used in SQL Server and those queries will need to be changed.

Here is a link with some info on converting Access to SQL

Converting Access Queries to SQL Server

Answer 2

You are right that, most of the time, you cannot just take the SQL of a query from Access and run it within SQL Server. It may work for very simple queries, but usually you need to tweak them.

There are a few steps I would take:

1. Extract your queries (which I presume are in your code), and re-create them in your Access database. Make sure they work there as normal Access queries.
   (you can for instance simply add some code to your app to print all queries to files so you don't have to mess with parameters, then just copy/paste them in your Access DB).
   The point is simply to have working queries within Access.

2. Use SSMA from Microsoft for helping you to move your queries to SQL Server. It does a good job of translating them into T-SQL.
   You may still have to convert some troublesome queries by hand, but it shouldn't be that many and usually the conversion is not difficult.

3. Once converted to T-SQL, just re-inject these working queries into your code, or keep the complex queries in SQL Server as views (which it usually be faster as SQL Server will have already created its execution plan, rather than your application sending raw SQL that the server needs to analyse).

4. As you pointed out, there could be some issues if your fields use some features that don't cross-over to SQL Server properly.
   Look at your tables in Access and do some cleanup before attempting to convert:

   • For booleans fields:
     Make sure you set their default values to 0 or 1 (they should not be empty).
   • Required fields must be non-null:
     Make sure that any fields that you have set as 'Required' does not contain any NULL values in its data.
   • Unique indexes cannot ignore Null:
     Check that your indexes are not set to be both 'Unique' and 'Ignore null'.
   • All tables must have clean primary keys:
     Make sure all your tables have a unique primary key that doesn't have Null values in their data.