Do you remove VBA function names in queries before upsizing queries (Upsizing problem)?
  v2.0 Posted at 20/10/2010 7:56 PM by System Account
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

Related rules

    Do you feel this rule needs an update?

    If you want to be notified when this rule is updated, please enter your email address: