Issues in Converting From Access 200 to SQL Server 7.0

[mohanrajh]

Hi Guys,
Access 200 & MS SQL Server 7.0 has some differences in Query Syntax (for eg. ORDER BY in Access is not supported in SQL Server 7.0, DISTINCTROW in Access is only DISTINCT in SQL Server, etc..), how does the conversion take place without compatability issues?
And after successfully converting from Access 2000, we do have to change the SQL Queries in the front end, isn't it?
Moreover '&' for Concatenating in Access is '+' in SQL Server, so isn't a bigger task to change every code(SQL) written in front end especially if the application is bigger and deals more with database?

Regards,
Mohan

[antares686]

Actually ORDER BY is the same in SQL 7 as Access 2K. As for Front End you should be able to leave alone as it will look at the tables (which are now linked from SQL Server) and the Jet DB engine will handle the query (client side) not SQL Server, but this is not the most effective way to use SQL. You would be better to write ADO connections within the Froms and Reports of Access and convert your logic to TSQL from JetSQL and even consider wrapping in SPs to maximize peformance. It is not extremly complicated but can be time consuming depending on the amount of processes you are dealing with.

Most people start out moving the tables to SQL and create a linked table in Access then slowly move towards coding into an ADO based application contained within Access. Then they may move to Web based or Executable based and get Access out of the loop.