Convert MS Access Queries into SQL Server views

Database migration from MS Access to SQL Server includes converting queries into the destination format. This whitepaper explores basic tips for this procedure. The target audience should have general knowledge in database management and experience in composing SQL queries.

The process begins with extracting Microsoft Access queries in form of SQL statements. For this purpose:

1. open the database in MS Access
2. expand Queries node in the left pane
3. right-click on the query and select "Design View"
4. in "View" menu select "SQL View" item
5. press "Ctrl"+"C" on the keyboard to copy the selected SQL statement to the Windows clipboard
6. insert it into a text file
7. repeat the procedure for all queries

Next step is to translate extracted statements according to MS SQL syntax. Below are required steps for this purpose:

1. replace 'expr1 & expr2' by 'expr1 + expr2', where expr1 and expr2 are string expressions
2. replace Microsoft Access boolean constants 'Yes' by 1, 'No' by 0
3. all date constants must be converted from #MM/DD/YY# to 'YYYY-MM-DD' format
4. there is no direct equivalent of First() and Last() aggregate functions in MySQL. If the querying field has ascending sort order, first() function should be replaced by min() and last() - by max(). Otherwise, if it is possible to control default sort order, these functions can be replaced as follows:

   select TOP 1 column_name from table_name order by something ASC
   

   and

   select TOP 1 column_name from table_name order by something DESC
   

5. replace MS Access built-in functions by MS SQL equivalents using the following table:

   Microsoft Access	SQL Server
   asc	ascii
   chr	char
   date()	getdate()
   InStr($position, $expr1, $expr2)	CHARINDEX($expr2, $expr1, $position)
   int	floor
   lcase	lower
   NZ($expr1, $expr2)	IIF($expr1 IS NULL, $expr2, $expr1)
   sgn	sign
   ucase	upper

If you need a solution for database migration from Access to SQL Server including table structures, data, indexes, relationships between tables and queries, take a look at Access to SQL Server converter