SQL-Transact conversion

[redbirdofthesouth]

When converting an Access database application from stand alone to client/server with SQL Server, is it necessary to modify the SQL code in queries to conform with SQL-Transact syntax rules if the queries live with the front end?
I am not clear on how to move the queries to the back end. If I figure that out will I need to change the SQL Code, then?
Thanks!

 

[SQLSister]

The answer to your question is a nice, firm, "It depends!"

Simple select statements will probably need little or no adjustment as long as you kept the column/table names the same.

If you are linking the SQL tables to an Access front end, you can continue to run the queries in Access. However, for performance reasons, you may want to convert them to stored procedures and run the stored procedures from Access instead.

If you convert them to stored procedures, you may or may not need to adjust the queries significantly depending on the way the query is written. The ones which need the most adjustment are the crosstab queries which Access has and which do not have an equivalent T-SQL command.

Another place where you will have to perform adjustments is when your queries run with a where clause from a specific field on a form. You can't do this directly in T-SQL as you can in Access. You have to create a stored procedure with one or more parameters and then pass the parameters to the stored procedure through your Access code.

T-SQL also doesn't use the IIF statement that Access SQL uses.

What we did when we converted was to leave all the queries in Access at first and then fully test the app and see what was running slowly (or worse, timing out). Those were the first queries we converted. Queries not significantly affecting performance remain in Access and we convert them a little at a time as time permits.