Solved

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

Posted on 2009-04-07
25
840 Views
Last Modified: 2012-05-06
I have SQL Server Database Query and I need to run this query on MS-Access, Please let me know how I can convert Sql syntax to MS-Access syntax.
0
Comment
Question by:vritti
  • 5
  • 5
  • 4
  • +5
25 Comments
 
LVL 2

Expert Comment

by:AlvinLim84
ID: 24094389
I thought the SQL server syntax is supported in MS Access?
0
 
LVL 5

Expert Comment

by:mfhorizon
ID: 24094401
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,
0
 

Author Comment

by:vritti
ID: 24094409
not all syntax supported by MS Access. Like in SQl for DateTime we need to use ' ' but in MS-Access it required ##.  
0
 
LVL 5

Expert Comment

by:mfhorizon
ID: 24094426
This article should also help you out:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23268105.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..."
0
 

Author Comment

by:vritti
ID: 24094438
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
0
 
LVL 77

Expert Comment

by:peter57r
ID: 24094540
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.
0
 
LVL 5

Expert Comment

by:mfhorizon
ID: 24095451
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?
0
 

Author Comment

by:vritti
ID: 24095975
I have checked the link which is given by you and this is only for .net1.1 version I am using .net 3.5.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 24121453
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
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 24121461
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 24121615
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...
0
 
LVL 18

Accepted Solution

by:
jmoss111 earned 125 total points
ID: 24121674
One thing that I forgot is that Access does allow the use of SQL Server Compatible Syntax by checking a check box in Tools|Options|Tables/Queries. Its's in the lower right part of the Tables/Queries window.

I've used it before but not to a great extent.

Regards,

Jim
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24121730
good call jim...
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 24122309
I really havent used it except a bit of experimentation. Have you used it much Mark?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24122470
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...
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 125 total points
ID: 24122836
Hope you need a tool to convert SQL Queries to Access:

http://www.brothersoft.com/sqlways-27442.html

Dont get confused because in the description they would have dealt about Migration of Databases.
When you install this tool, an utility named SQL Converter will be installed which will help you out on the Query conversion and that's for almost all databases AFAIK.
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 125 total points
ID: 24125730
Just to throw in my $0.02 -- there is no easy way to do it.

What are you trying to do, just front-end the SQL db with Access, or downsize?

If you are doing a front-end, you can turn the queries into stored procedures and such and use ADO to run it. Another thought is to look into SQL Express.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 24125746
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?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24127834
You say "I have this query" if it is just the one, then post it, we can probably do it for you...
0
 

Author Comment

by:vritti
ID: 24128477
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
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 125 total points
ID: 24129219
Well, the database can be converted / migrated, and unless views are converting / creating computed columns, they should be OK as well. So it becomes a question of Stored Procedures and/or any script that you front end is creating. That is where the efforts will be. Especially any code you have written as part of your application space - nothing (?) will convert that better than you can do with some elbow grease, a bit of time, a few errors along the way, and some realistic goals as to how best it can be achieved.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now