how can I convert SQL Server Query into MS-Access Query by using any tool or program

I thought the SQL server syntax is supported in MS Access?

Hi,
If you are in hurry try this software which is $20 only and provide a context sensitive, simple way to perform SQL queries on the fly. http://quick-query.quick-query.qarchive.org/ I am sure it must help.

If you are not in hurry let me know which kind of queries are these DDL, DML, or DRL.

Regards,

not all syntax supported by MS Access. Like in SQl for DateTime we need to use ' ' but in MS-Access it required ##.  

This article should also help you out:
https://www.experts-exchange.com/questions/23268105/Convert-SQL-statement-in-Access-Query.html

From above articles two important phrases.
"In Access, you need to translate CASE expressions into IIf(), Switch(), and/or Choose() statements. In this case, Switch() works nicely. The last condition, True, is equivalent to the final ELSE."

"switch to Booleans"yes/no" fields..."

The query can be DDL,DML or DRL. Actually I have a project which is working on SQL server fine. Now my requirement is that it have to be work with MS-Access. So I am searching a tool or program which I can use in my project and it convert SQl syntax to MS Access Syntax

I think the chances of finding such a tool are close to zero.  T_SQL is such a big language compared to JET that any such tool would be scratching the surface of what is required.

Why don't you try tool i mentioned http://quick-query.quick-query.qarchive.org/?
Read the tool details first and why not to try?

I have checked the link which is given by you and this is only for .net1.1 version I am using .net 3.5.

Hi vritti,

The following hyperlink contains a "cheatsheet" for conversion of Access and T-SQL which will give you a lot of the differences:


http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/Quick-Access-JET-SQL-to-T-SQL-Cheatsheet.aspx

Regards,

Jim

One more:

http://sqlserver2000.databases.aspfaq.com/what-are-the-main-differences-between-access-and-sql-server.html

I know there are migration packages from Access to SQL Server, but do not know of any the other way around.

Further, the way access works, it would be difficult to dynamically convert into the database without writing a SQL translator from within access to suck in a script and the convert before commiting / saving as a new query. That is not easy to do.

possibly the best approach is to write a procedure in SQL to output a text file suitable to copy and paste into Access.

As for tables, then there are tools like sobolsoft :  http://www.sobolsoft.com/accessmssql/    and  http://dbconvert.com/convert-access-to-mssql-pro.php

For scripts, you can try things like : http://www.altova.com/downloadtrial4DatabaseSpy.html   but found it is not so much a convert, but an interpreter for the target database.

And a fair selection of various script "helpers" and converters : http://script.software.informer.com/download-script-ms-sql-query/  but again, not so sure they will actually migrate a script in SQL into an appropriate query in Access.

Hope you do find something above, but think that if you can migrate the database, then might be one of those 80/20 things. Spend time on migrating the scripts by writing some code in SQL server and try to get the bulk done that way and spend a bit of quality manual interface to do the tricky bits...

good call jim...

I really havent used it except a bit of experimentation. Have you used it much Mark?

Much the same as you - I do recall a time where it was the default setting for the database (when creating a new database, can set it as the default for all tables) for anything we did, but that was a while back, and mainly because of reserved words and wildcards when we planned the moved TO sql rather than FROM sql.

But if memory serves me right, then things like dates could still be referred to by #3/3/2009# so it did not change to Ansi 92 mode syntax for functions, nor did it resort to the SQL version of functions such as isnull() - SQL has two arguments, Access has one.

So might not be so helpfull after all now that I think about it...

I agree with Jim, not me but the other one... And why would you even want to do that? By the way, just what are you trying to accomplish?

You say "I have this query" if it is just the one, then post it, we can probably do it for you...

Actually I have a Project which is running on MS-SQl 2005, Now this whole project should be shift to MS-Access database. So I have a lots of SQL statements