Rules

Secret ingredients to quality software

Do you remove VBA function names in queries before upsizing queries (Upsizing problem)?

Last updated by SSW.Rules.SharePointExtractor on 22 Feb 2021 02:04 am (9 months ago) See History

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

Adam CoganAdam Cogan

We open source. Powered by GitHub