Home > Documentation > MS Access to SQL Server

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 MS Access to MS SQL including data, indexes, foreign keys and queries, take a look at Access to MS SQL converter