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

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.
vrittiAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
jmoss111Connect With a Mentor Commented:
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
 
AlvinLim84Commented:
I thought the SQL server syntax is supported in MS Access?
0
 
mfhorizonCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
vrittiAuthor Commented:
not all syntax supported by MS Access. Like in SQl for DateTime we need to use ' ' but in MS-Access it required ##.  
0
 
mfhorizonCommented:
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
 
vrittiAuthor Commented:
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
 
peter57rCommented:
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
 
mfhorizonCommented:
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
 
vrittiAuthor Commented:
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
 
jmoss111Commented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
Mark WillsTopic AdvisorCommented:
good call jim...
0
 
jmoss111Commented:
I really havent used it except a bit of experimentation. Have you used it much Mark?
0
 
Mark WillsTopic AdvisorCommented:
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
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
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
 
Jim P.Connect With a Mentor Commented:
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
 
jmoss111Commented:
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
 
Mark WillsTopic AdvisorCommented:
You say "I have this query" if it is just the one, then post it, we can probably do it for you...
0
 
vrittiAuthor Commented:
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
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.