Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2009-04-07
25
Medium Priority
?
878 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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
 
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 500 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 500 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 500 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 500 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

609 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