DEVELOPMENT SERVER
Rules to Better Access Databases

The Upsizing Tools do not try to upsize Microsoft Access query that includes VBA function names that don't have their equivalent Transact-SQL functions. The upsizing result will depend on Microsoft Access version (2000/2002/2003) and SQL Server Version (2000/2005). The following varieties of queries will not upsize:

• Queries referencing value in control, for example Forms![FormName]![ControlName] (Access 2000)
• Select queries that take parameters (Access 2000)
• Select queries where parameter used more than once (All versions of Access)
• Select queries referencing Format function (All versions of Access)

You have to manually edit SQL definition in Microsoft Access (remove or replace keyword) and modify view/stored procedure/function T-SQL in SQL Server after upsizing.

SELECT Orders.OrderID,
    "Order Subtotals".Subtotal, 
    FORMAT(ShippedDate,'yyyy') AS Year 
FROM Orders 
INNER JOIN "Order Subtotals" 
    ON (Orders.OrderID="Order Subtotals".OrderID);

Figure: Bad example of Access query with FORMAT keyword

SELECT Orders.OrderID,
    "Order Subtotals".Subtotal, 
    YEAR(ShippedDate) AS [Year] 
FROM Orders 
INNER JOIN "Order Subtotals" 
    ON (Orders.OrderID="Order Subtotals".OrderID)

Figure: Good example of SQL Server view with YEAR keyword
Upsizing PRO will check this rule