CODEWITHSUNDEEP
sql-serverms-access
James Khan
James Khan

Reputation: 841

MS Access Queries Conversion to Sql Server

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 ?

Upvotes: 0

Views: 1001

Answers (1)

Mark C.
Mark C.

Reputation: 6450

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.

Upvotes: 4

Related Questions