0

I am converting lots of access queries to sql server stored procedure. So the sql need to meet the t-sql standard. For example IIF etc Is there a tool that can convert big access queries to t-sql ? What is the best way of doing this ?

Improve this question
1
  • Sorry, but I believe that best way is to translate query by query manually. Commented Jun 20, 2014 at 13:06

1 Answer 1

Reset to default
4

As far as a "tool" that will just convert the queries for you, I'm not aware of one. Neither is anyone on this thread or this site.

There are a couple places I can direct you, though, that can possibly help with the transition.

Here is a cheat sheet you can use as a quick glance when converting your queries.

If your queries use any [Forms]! references, there could also be an issue with that. (I've never tried it, but I am going to assume it doesn't work.)

This resource has probably the most detailed explanations on things you might need to learn in SQL Server. From stored queries, to handling NULLs to some of the other differences. There are also differences in MS Access SQL compared to T-SQL. Gordon Linoff briefly describes 10 important differences in his blog.

  1. Access does not support the case statement, so conditional logic is done with the non-standard IIf() or Switch() functions.
  2. Access requires parentheses around each pair-wise join, resulting in a proliferation of nesting in from clauses that only serves to confuse people learning SQL.
  3. Access join syntax requires the INNER for INNER JOIN. While it may be a good idea to use inner for clarify, it is often omitted in practice (in other databases).
  4. Access does not support full outer join.
  5. Access does not allow union or union all in subqueries.
  6. Access requires the AS for table aliases. In most databases, this is optional, and I prefer to only use as for column aliases. Ironically, the use of as for table aliases is forbidden in Oracle.
  7. Access uses double quotes to delimit strings (as opposed to single quotes) and is the only database (to my knowledge) that uses & as a string concatenation operator.
  8. Access uses * for the wildcard in like rather than %.
  9. Access allows BETWEEN AND . This is allowed in other databases, but will always evaluate to false.
  10. Access does not support window/analytic functions (using the over and partition by clauses).

In sum, no, there is no tool that I have seen.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Some corrections and extensions to Mr. Linoff's blog comments. RE: (1) Access also has a Switch() function that is a closer match to CASE .. WHEN and can avoid multiple nested IIf() functions. RE: (6) No, the AS keyword is not required for table aliases; it is optional. RE: (7) Access SQL supports both single-quotes and double-quotes for string literals. Access SQL also supports both & and + for string concatenation, although they handle Nulls differently.
Thanks! Great points. I often forget Switch() exists. Feel free to edit my answer if you see fit anywhere! 2 minds are greater than 1.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.