Clever Geek Handbook

MS Access 2007 Queries Won't Run on SQL Server 2008

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

Today I realized that my database could not be processed sequentially by MS Access 2007. So I decided to go for SQL Server 2008 R2 Express with the Upsizing wizard and it will work great!

However, when I tried to trigger various parameters of my already well-developed application, it kept throwing an error every time the query was run on SQL Server.

I realized that many SQL materials supported by MS Access are not supported by MS SQL Server

For example: query with date, to represent the date format, when we use '#', SQL Server 2008 will not recognize it.

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

All of these queries worked fine with Access 07

I'm sure there must be some method for SQL Server to be able to understand MS access requests.

Or will I need to edit my entire application? It will be as good as digging a mine to make gold.

I have changed all data access objects like reader, adapter, command, connect to SQL data objects using System.Data.SqlClient

.

So this is not a problem.

Please help me as soon as possible.

Thank.

+3


source to share


2 answers


You cannot force SQL Server to run 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 for use in SQL Server.

There will be some queries that can be carried over directly, but since you noticed the queries with date and even some of the aggregate functions (First (), etc.) are not used in SQL Server and these queries will need to be modified.



Here is a link to some information on converting Access to SQL

Converting SQL Server Access Queries

+5


source


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

Several steps can be taken:



  • Extract your queries (which I assume are listed in your code) and re-create them in your Access database. Make sure they work like normal Access queries there.
    (you can, for example, just add code to your application to print out all file requests so you don't have to mess around with the parameters and then just copy / paste them into your access database).
    It's just a matter of having working queries in Access.

  • Use SSMA from Microsoft to help you move your queries to SQL Server. It does a good job of translating them to T-SQL.
    You still have to translate some nasty queries by hand, but there shouldn't be that many, and usually the conversion isn't difficult.

  • After converting to T-SQL, simply re-insert those production queries into your code, or store complex queries in SQL Server as views (which is usually faster, since SQL Server has already generated its execution plan, not your application submitting raw SQL, which the server should analyze).

  • As you pointed out, there can be some problems if your fields use some functionality that is not migrating to SQL Server as expected.
    Take a look at your tables in Access and do some cleanup before trying to convert:

    • For booleans fields:
      Make sure you set your default values ​​to 0 or 1 (they must not be empty).
    • Required fields must not be empty:
      Make sure that any fields you specify as "Required" do not contain NULL values ​​in their data.
    • Unique indexes cannot ignore Null:
      Make sure your indexes are not set to either "Unique" or "Ignore null".
    • All tables must have clean primary keys:
      Make sure all your tables have a unique primary key that does not have null values ​​in their data.
+2


source






More articles:


All Articles