MS-Access / Getting Started

---

Comparing Query Conversion in the Upsizing Wizard and SSMA

In SQL Server, you are not allowed to have the SQL ORDER BY in a View (which conforms with the SQL standard), and for that reason, the Upsizing Wizard creates a function to return a result, as shown in the following:

CREATE FUNCTION [dbo].[Current Product List] ()
RETURNS TABLE
AS RETURN (SELECT TOP 100 PERCENT "Product List".ProductID,
"Product List".ProductName
FROM Products AS "Product List"
WHERE ((("Product List".Discontinued)=0))
ORDER BY "Product List".ProductName)
GO
EXEC sys.sp_addextendedproperty @name=N'MS_AlternateBackThemeColorIndex',
@value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'FUNCTION',@level1name=N'Current Product List'
GO

The Upsizing Wizard also makes extensive use of creating extended properties, which are used to record display settings when displaying the function on a Query grid. There is a way to put an ORDER BY inside a View, and SSMA will create a View for this query by using this strategy, which involves including the TOP... WITH TIES statement, as shown here:

CREATE VIEW [dbo].[Current Product List]
AS
    SELECT TOP 9223372036854775807 WITH
    TIES [Product List].ProductID,
    [Product List].ProductName
    FROM Products AS [Product List]
    WHERE ((([Product List].Discontinued) = 0))
    ORDER BY [Product List].ProductName
GO

The very large number, 9223372036854775807, is used to avoid the need to say TOP 100%, for reasons of internal efficiency.

If you are using an ADP, then the previously mentioned approach with functions can return updateable data, but if you want to use an Access database, you can only utilize a function by using a pass-through query (which is read-only); you cannot link an Access database directly to a function.

Another nice feature of the Upsizing Wizard is that when it converts a query that contains a reference to an Access parameter, it generates a parameterized function, as shown below:

CREATE FUNCTION [dbo].[Invoices Filter]
(@Forms___Orders___OrderID1 varchar (255)
)
RETURNS TABLE
AS RETURN (SELECT Invoices.*
FROM Invoices
WHERE (((Invoices.OrderID)=@Forms___Orders___OrderID1)))
GO

The original SQL used in the Access query was as follows:

SELECT Invoices.*
FROM Invoices
WHERE (((Invoices.OrderID)=[Forms]![Orders]![OrderID]));

SSMA is not able to handle this kind of query with references to screen parameters and does not convert the query.

In summary, if you are planning to use an ADP, then the Upsizing Wizard offers a large number of advantages over SSMA when converting queries; but if you are planning to use an Access database (.accdb or .mdb) linked to SQL Server, then you will find that SSMA produces more favorable results when converting your SQL; you can, of course, run a conversion through both tools and copy and paste the preferred results between the systems.

SSMA strengths and weaknesses
If you want better control of the Data Type mappings, and you want to ensure that Required fields are correctly translated to NOT NULL column attributes, SSMA offers a better choice than the Upsizing Wizard. The support for migration onto Schemas is also a very attractive feature of the product, but it would benefit from a more structured approach that permits a single migration to split multiple tables over schemas and thus preserve the relationships during the migration process.
The only weakness we came across in SSMA was in attempting to translate a UNIQUE INDEX which ignores Nulls. That can be easily avoided by checking for this in your database prior to conversion.