Upsizing with the Trial-and-Error Approach

An initial test of the Upsizing Wizard lets you see how much work is in store to upsize your application. For a complex Jet application, the test drive might convince you to abandon a full-fledged upsizing operation and use SQL Server tables linked to your Jet front end. In this case, Chapter 19, "Linking Access Front-Ends to Jet and Client/Server Tables," describes the quick and easy way to gain most of the advantages of an SQL Server back end.

If you don't have a production Jet application to test and you've created the queries, forms, and reports used as examples in the preceding chapters in a copy of Northwind.mdb, you have a good starting point for determining what types of Jet objects the Upsizing Wizard can handle without modification.

Note

If you didn't perform all the exercises, Upsize22.mdb in the \Seua11\Chaptr22 folder of the accompanying CD-ROM contains most of the example objects created in Chapters 5 through 19. Between...And replaces instances of Like expressions in the Upsize22.mdb queries.

Tip

Set Access 2003's macro security level to Low before starting the upsizing process. If you specify Medium as the security level, the you must click the Enable Macros button for each application object that the Wizard copies to the project file.

To give the Upsizing Wizard a test drive with a new SQL Server database, do the following:

1. Create a backup copy of the database you plan to upsize, unless you're using the Upsize22.mdb sample database. You can upsize either a single-file or split (front-end/back-end) application. The upsizing process doesn't affect the source .mdb file under ordinary circumstances, but there's always the chance of an extraordinary occurrence.

2. Log on to the database as a member of the Admins group. You don't need full Admins authority, but you do need at least Read Data and Read Design permissions for all objects in the source database(s).

3. If any Jet database table or query objects are hidden, choose Tools, Options; click the View tab; and mark the Hidden Objects check box in the Show frame of the Options dialog. To upsize the hidden objects, select Window, Unhide to open the Unhide Window dialog. Then, select the window(s), and click OK to unhide them.

4. Choose Tools, Database Utilities, Upsizing Wizard to open the Wizard's first dialog. Accept the default Create a New Database option, and click Next.

5. In the second Wizard dialog, accept the default (local) server for MSDE or open the list box and specify the NetBIOS computer name of the machine running SQL Server.

6. If you're logged on as a Windows user having administrative privileges for the destination SQL Server, mark the Use Trusted Connection check box. Alternatively, use sa or your assigned SQL Server administrator login ID and type your password, if the server uses SQL Server security. By default, MSDE2000 requires a trusted connection, which uses Windows integrated security for authentication.

7. Accept or change the name of the new SQL Server database. This example uses UpsizeSQL as the database name (see Figure 22.1). Click Next.

   Figure 22.1. Specify (local) or the NetBIOS name of the computer running SQL Server, the type of authentication (integrated Windows or SQL Server), and the name of the new SQL Server database in the second Wizard dialog.

   

8. In the third Wizard dialog, click the >> button to move all tables from the Available Tables to the Export to SQL Server list (see Figure 22.2). The HRActions table and all tables with a tbl prefix are sample tables created in earlier chapters. Click Next.

   Figure 22.2. Select all Jet tables for upsizing, unless you have a good reason to do otherwise.

   

9. In the fourth dialog, accept the defaults for table attributes, and select Yes, Let the Wizard Decide in the Add Timestamp Fields to Tables list. Unless you have tables with more than a 100,000 or more records, don't mark the Only Create the Table Structure; Don't Upsize Any Data check box (see Figure 22.3). Click Next.

   Figure 22.3. Default table attributes are satisfactory for most upsizing operations, but it's a good practice to let the Wizard add timestamp fields to upsized tables with Jet Memo and OLE Object fields.

   

10. The fifth dialog offers the choices of creating a new project or linking SQL Server tables to the Jet application. Select the Create a New Access Client/Server Application option. The Wizard proposes to name the front-end file MdbNameCS.adp. For this initial example, the name is UpsizeSQL.adp (see Figure 22.4). Click Next.

    Figure 22.4. Specify a new Access project and give the .adp file a name in the fifth Wizard dialog.

    

    Tip

    Clear the Save Password and User ID box if you want to maintain the security of your back-end database. Default integrated Windows security lets you control database access by the user's group membership in a Windows 2000+/NT domain. If you specified SQL Server security in step 5, users must type their pre-assigned login ID and password to connect to the database.

    To learn more about granting users access to your upsized database, see "Securing Projects with the MSDE 2000 Login/User Tool," p. 936 .

    
    

11. Accept the Open the New ADP File option and click Finish to start the upsizing operation.

The Wizard begins the upsizing process and displays a progress dialog (see Figure 22.5).

Figure 22.5. The progress dialog briefly describes each Upsizing Wizard operation. The width of the progress bar is based on the number of objects upsized, not the time required for upsizing.

Note

The Wizard reports that it's adding triggers to the tables, regardless of whether you specify the default Use DRI (declarative referential integrity) or Use Triggers option in the fourth Wizard dialog.

After the Wizard copies the table structures, data, and extended properties, you receive a series of error messages when the Wizard starts upsizing DAP. Figure 22.6 illustrates a typical DAP error message, which occurs because the Wizard can't handle DAP that use relative paths for their data source. Click OK after reading each message to complete the upsizing process.

Figure 22.6. Northwind.mdb's sample data access pages fail to upsize under Windows XP SP1 because their use of a relative path (Northwind.mdb, instead of C:\FullPath\Northwind.mdb) prevents opening the PageName.htm file.

When the Wizard completes copying forms and other application objects, which is a much slower process than copying other objects (except large tables), the Upsizing Wizard Report opens. Upsizing Upsize22.mdb takes about 10 minutes on a moderately fast (833-MHz Pentium III) computer. The report snapshot file (Upsize22.snp) for initial upsizing of Upsize22.mdb is 43 pages, of which pages 32 43 contain results for queries. (Your page numbers might differ.) Figure 22.7 illustrates results for two successfully upsized queries. The Wizard uses SQL Server quoted ("...") identifiers for object names with spaces instead of the more conventional square bracket ([...]) pairs.

Figure 22.7. Report elements for a UNION query upsized to a stored procedure and a simple SELECT query upsized to a view.

Tip

Look for the Wizard's upsizing report snapshot files in Access's default database folder, which you specify on the General page of the Options dialog, not in the working folder that contains the source .mdb or destination .adp file.

Note

Access 2003's Upsizing Wizard can handle Jet UNION queries; Access 2000 and earlier Wizard versions could not. The strange-appearing ' ' AS "__ __" column identifiers are required, because SQL Server requires the result set of the UNION SELECT statement to return the same number of columns as the initial SELECT result set, even if the columns are empty.

Following are the Upsizing Wizard's limitations and initial workarounds, where available, based on the report for the initial test run with Upsize22.mdb:

• The Wizard doesn't remove Jet SQL's DISTINCTROW reserved word or convert it to T-SQL's DISTINCT modifier. Access 95 and earlier automatically added DISTINCTROW to all SELECT queries, so this problem is likely to occur only with seasoned Jet applications. The "Jet's DISTINCTROW and T-SQL's DISTINCT Keywords" section later in the chapter compares the difference in effect of these two modifiers on query result sets. Changing DISTINCTROW to DISTINCT solves this problem, but might have side effects, such as making the query result set not updatable. You seldom need the DISTINCT modifier; if you do, add it to the upsized query when you test it.

• The Wizard won't upsize Jet crosstab queries, because SQL Server doesn't support Jet's SQL's PIVOT and TRANSFORM key words. The workaround for this problem isn't simple, as demonstrated in the later "Emulating Jet Crosstab Queries with T-SQL" section.

• The Wizard bails out when it encounters VBA reserved words, such as Format, for which T-SQL has no direct equivalent. The qry1997OrdersByCountryPT, qryMonthlyOrders1997, qryMonthlyOrdersParam, qryMonthlySales1997, qryOrderAmount, and Sales by Year queries use the Format function. The initial workaround for these queries is to remove the function and its format definition string to verify that the unformatted query is upsizable. If WHERE, GROUP BY, or ORDER BY clauses use the Format function, the query result set won't be valid.

• Nested queries fail to upsize when the source query (also called the outer query) won't upsize. Fixing the outer query solves the problem, if the nested (inner) query that's specified in the FROM clause can be upsized.

• Jet SELECT and action queries that use the IN predicate to specify another .mdb file won't upsize. The four qryOakmont... queries can't be upsized, because the IN predicate specifies the Oakmont.mdb file. The only workaround is to upsize the Oakmont.mdb database, and then write queries that use three-part SQL Server names to specify tables in the OakmontSQL database.

  For more information on three-part names, see "Working with Tables in Another Database," p. 878 .

  
  

• Action queries with parameters don't upsize. SELECT queries with parameters upsize to in-line, table-valued functions with parameters. Parameterized SELECT queries with spaces in the parameter names, such as qryStateMailList, don't upsize.

  To review in-line functions that use parameters, see "Creating a Parameterized Table-Returning Function," p. 825 .

  
  

• The Wizard fails to upsize queries with column alias names that are SQL Server reserved words. For example, OrderDate AS Date or CostPerUnit AS Costas a member of a field list prevents upsizing the query. In most cases, surrounding reserved words with [...] delimiters as in OrderDate AS [Date] solves the problem.

• The Wizard won't upsize queries that aren't executable because of a table- or field-naming error. The tblOrders table doesn't have Ship... fields, so upsizing qryUQtblOrders1 fails. The SalesOrders query fails because Table1 is missing. Delete test queries or action queries you ran once to modify tables.

Modifying complex queries to work around upsizing problems is a tedious process, especially if you have a large number of queries that fail. Be sure to test each query whose Jet SQL statement you modify to verify the changes you make. Deleting crosstab and other queries that the Wizard can't upsize is optional.

Running a Second Upsizing Pass

After performing the first set of query fixes and temporary workarounds, you must run the Upsizing Wizard again. The sample Upsize22A.mdb file has most of the fixes described in the preceding section.

To make a second upsizing pass from scratch, do the following:

1. Close all open objects in the new .adp file except the Database window.

2. Choose Tools, Database Utilities, Drop SQL Database. A message box asks you to confirm dropping the current database (UpsizeSQL for this example).

3. Click Yes to drop SQL Server's reference to the database files and delete the corresponding .mdf and .ldf files. Your database disconnects, and the Tables and Queries pages of the Database window empty.

4. Close the project, and use Windows Explorer to delete the .adp file you created (UpsizeSQL.adp for this initial example).

5. Open Upsize22.mdb (or whatever file you're upsizing) and repeat steps 4 11 of the earlier "Performing an Initial Test of the Upsizing Process" section.

Table 22.1 is a scorecard for the Upsizing Wizard's successive passes on the Upsize22.mdb and Upsize22A.mdb queries. The numbers in the Pass 2 column reflect modification or deletion after the first pass of queries that the Wizard won't attempt to upsize. (Upsize22a.mdb generates the Pass 2 data).

Table 22.1. Success and Failure of Upsize22.mdb Queries in Two Trials with the Upsizing Wizard

Query Upsize Status	Pass 1	Pass 2
Successfully upsized	29	46
Failed (DISTINCTROW)	13	0
Failed (PIVOT...TRANSFORM)	5	0
Failed (VBA Format function)	5	0
Failed (missing source query)	4	0
Failed (tables in another database)	4	0
Failed (missing field or table)	2	0
Failed (mishandled parameter)	1	0
Failed (other reasons)	1	2

At this point, only two of Upsize22.mdb's queries fail to upsize, so it's not an efficient use of your time to attempt to modify the Jet SQL statements and rerun the Upsizing Wizard. The better approach is to correct the remaining problems in the new project, UpsizeCS.adp, which is used in the following examples.

Note

When you click the Check SQL Syntax button or save the upsized view, function, or stored procedure, the SQL Server query parser adds the dbo. prefix to each table name in the T-SQL statement.

Correcting Wizard Errors

The first of the two queries that fail to upsize for "other reasons" is qry1997OrderDataPT. This nested query, which requires the Order Details Extended query as a data source, is intended as the data source for a PivotTable to display quarterly or monthly orders by employee and category. Figure 22.8 shows the upsizing report entry for the query. This error relates to the decision by the Upsizing Wizard's developers to upsize Jet queries with ORDER BY clauses to in-line, table-valued functions instead of views with the TOP 100 PERCENT modifier. The Jet Order Details Extended query upsizes to a view, but the Wizard doesn't handle references to functions correctly.

Figure 22.8. Failure to upsize the qry1997OrderDatePT query results from the [Order Details Extended]() "Order Details Extended" fragments.

Jet SQL

The Jet SQL version of the qry1997OrderDataPT query is

 SELECT Employees.LastName AS Name,       Orders.OrderDate AS [Date],       Orders.ShipCountry AS Country,      [Order Details Extended].ExtendedPrice AS Orders,       Products.ProductName AS Product,       Categories.CategoryName AS Category FROM Employees    INNER JOIN (Categories       INNER JOIN ((Orders          INNER JOIN [Order Details Extended]          ON Orders.OrderID =                [Order Details Extended].OrderID)             INNER JOIN Products             ON [Order Details Extended].ProductID =                 Products.ProductID)    ON Categories.CategoryID = Products.CategoryID)          ON Employees.EmployeeID = Orders.EmployeeID; 

The Wizard erroneously translates the FROM clause of the Jet SQL statement to

 FROM Employees    INNER JOIN (Categories       INNER JOIN ((Orders          INNER JOIN [Order Details Extended]()             "Order Details Extended"          ON Orders.OrderID =               "Order Details Extended".OrderID)             INNER JOIN Products ON [Order Details Extended]()       "Order Details Extended".ProductID =                Products.ProductID)    ON Categories.CategoryID = Products.CategoryID)       ON Employees.EmployeeID = Orders.EmployeeID 

The error is mixing delimiter types in the statement that calls the Order Details Extended function. Changing [Order Details Extended]() to "Order Details Extended"() solves the problem. The nested query failed to upsize, so you can't just edit the T-SQL for the query. Following is what you must do to fix the problem:

• Replace the Order Details Extended function with a view. Copy the T-SQL statement of the function to the Clipboard, delete the function, create a new view in the project designer, and paste the T-SQL statement in the view's SQL pane. The nested query didn't upsize, so you must copy the original Jet SQL Statement to the Clipboard, create a new view, and paste the text to the SQL pane of the view.

  T-SQL

  The T-SQL for the view of the Order Details Extended query is

   SELECT TOP 100 PERCENT dbo.[Order Details].OrderID,    dbo.[Order Details].ProductID,    dbo.Products.ProductName,    dbo.[Order Details].UnitPrice,    dbo.[Order Details].Quantity,    dbo.[Order Details].Discount,    CONVERT(money, (dbo.[Order Details].UnitPrice *       dbo.[Order Details].Quantity) *       (1 - dbo.[Order Details].Discount) / 100) *       100 AS ExtendedPrice       AS ExtendedPrice FROM dbo.Products    INNER JOIN dbo.[Order Details]       ON dbo.Products.ProductID = dbo.[Order Details].ProductID ORDER BY dbo.[Order Details].OrderID 

  You can remove / 100 and * 100 without affecting the values in the ExtendedPrice column.

• Copy the original Jet SQL Statement to the Clipboard, create a new view, and paste the text to the SQL pane of the view.

Alternatively, you can use the existing function as the inner query and modify the outer query. Add the function name with empty parentheses to the INNER JOIN element as follows: INNER JOIN [Order Details Extended]()[Order Details Extended]. (Square bracket delimiters work if you don't combine them with double-quote delimiters.) If you use AS to explicitly declare the alias, clicking Check SQL Syntax removes it.

The better of the two preceding options is to change the function to a view, because multiple nested queries or the Record Source property value of forms and reports might depend on the source query.

The qryCTWizSource view illustrates the importance of testing each successfully upsized object in the project designer to verify the correctness of column names and data values. The qryCTWizSource view upsizes, but contains an upsizing error in this case a spurious alias for the ProductID column. Following is the offending SELECT statement's column list with the bad alias in bold:

 SELECT dbo.Orders.OrderDate,    [Order Details Extended].ProductID       AS [_Order Details Extended.ProductID_],    [Order Details Extended].ProductName, [Order Details Extended].ExtendedPrice 

Remove the alias and the view executes correctly.

Conforming Computed Columns to the ANSI SQL Standard

Jet SQL lets you use the value of one computed column as a source for another computed column. It's common to use such compound-computed columns to store values that include sales or value-added taxes.

Jet SQL

The following sample field list with multiple aliased computed columns works when you use Jet SQL but fails with T-SQL:

 SELECT OrderTotal * 0.06 AS StateTax,    OrderTotal * 0.01 AS CountyTax,    OrderTotal * 0.005 AS CityTax,    StateTax + CountyTax +       CityTax AS Taxes,    OrderTotal + Taxes AS InvoiceTotal FROM Orders 

The ANSI-92 SQL standard requires that each member of the SELECT column list must have an unambiguous reference to a field of a table specified in the FROM clause. The computed Taxes column of the preceding example fails this test, because it's defined only in the query and not in the table, so SQL Server's query parser won't compile the statement.

Figure 22.9 shows the error message the query parser returns when the Upsizing Wizard encounters the Jet qryOrderAmount query, which computes freight cost as a percentage of the order amount for each order. (The Format() function to display percent was removed from the query prior to upsizing.) This query uses the VBA CCur function to convert the Jet data type of the Amount from Double to Currency.

Figure 22.9. The qryOrderAmount query fails to upsize to a function, because the FreightPct column is dependent on the computed Amount column.

Jet SQL

The Jet SQL statement for the qryOrderAmount query is

 SELECT Customers.CompanyName,       Customers.PostalCode, Customers.Country,       Orders.OrderID, Orders.OrderDate,       Sum(CCur([UnitPrice]*[Quantity]*          (1-[Discount]))) AS Amount,       Orders.Freight, [Freight]/[Amount] AS FreightPct FROM (Customers       INNER JOIN Orders          ON Customers.CustomerID = Orders.CustomerID)       INNER JOIN [Order Details]          ON Orders.OrderID = [Order Details].OrderID GROUP BY Customers.CompanyName, Customers.PostalCode,    Customers.Country, Orders.OrderID,    Orders.OrderDate, Orders.Freight ORDER BY Orders.OrderID; 

The column definition that conflicts with ANSI SQL is in bold type.

You can correct the problem in the source query by substituting the Sum...AS Amount aggregate statement for the [Amount] value in the second computed column. If you correct the source query, you must rerun the Upsizing Wizard. It's faster to create a new view in the project designer by doing the following:

1. Copy the Jet SQL statement of the source query to the Clipboard.

2. Create a new view in the upsized project, click the SQL button, and paste the statement to the SQL pane.

3. Substitute the T-SQL CONVERT or CAST function for VBA type conversion statements, if they are used in value expressions. For this example, the CAST (Expression AS money) function substitutes for the VBA CCur (Expression) function.

4. Temporarily remove the element of the column definition that prevents compiling the view, /[Amount] for this example. Click Check SQL Syntax to verify your changes. The project designer automatically adds TOP 100 PERCENT to accommodate the ORDER BY clause (see Figure 22.10).

   Figure 22.10. The first step in conforming a Jet SQL query with computed column values based on aliased columns is to substitute T-SQL functions for unsupported VBA functions and test the result without the offending aliased value.

   

5. Substitute the expression of the calculated column for the alias name you deleted. For this example, / SUM((dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)) substitutes for /[Amount]. The CAST function isn't required here because the column value is a decimal fraction, not a monetary amount.

   T-SQL

   The final T-SQL statement for the qryOrderAmount view is

    SELECT TOP 100 PERCENT dbo.Customers.CompanyName,       dbo.Customers.PostalCode, dbo.Customers.Country,       dbo.Orders.OrderID, dbo.Orders.OrderDate,       SUM(CAST((dbo.[Order Details].UnitPrice *          dbo.[Order Details].Quantity) *         (1 - dbo.[Order Details].Discount) AS money)) AS Amount,       dbo.Orders.Freight,       dbo.Orders.Freight /          SUM((dbo.[Order Details].UnitPrice *          dbo.[Order Details].Quantity) *         (1 - dbo.[Order Details].Discount)) AS FreightPct FROM dbo.Customers    INNER JOIN dbo.Orders       ON dbo.Customers.CustomerID =          dbo.Orders.CustomerID    INNER JOIN dbo.[Order Details]       ON dbo.Orders.OrderID =          dbo.[Order Details].OrderID GROUP BY dbo.Customers.CompanyName,    dbo.Customers.PostalCode, dbo.Customers.Country,    dbo.Orders.OrderID, dbo.Orders.OrderDate,    dbo.Orders.Freight ORDER BY dbo.Orders.OrderID 

6. Verify your changes, and save the view with the original query name, qryOrderAmount for this example.

7. Run the view and verify that the result set is identical to that of the original Jet Query. In this case, the FreightPct column is formatted as money, because the extended Format property value for the computed column is $#,##0.00;($#,##0.00). The computed FreightPct column inherits this format from the upsized Jet format applied to the Freight column of the Orders table.

8. To change the format of the FreightPct column, right click anywhere in the project designer window, and choose Properties to open the Properties dialog for the view. Click the Columns tab, select FreightPct from the Column Name list, and select Percent from the Format list (see Figure 22.11). Extended property values apply only to Access objects, such as datasheets.

   Figure 22.11. Set the extended Format property value of columns you want to display as percentages in Datasheet view. Format property values override the default display of SQL Server formatted data types, such as money, smallmoney, and datetime. You also can specify the number of decimal digits the datasheet displays by setting the value of the Decimal Places property.

   

9. Close the Properties dialog, save the change to the view, and rerun the view. Figure 22.12 shows the resulting datasheet with the column sequence rearranged to match that of the column list.

   Figure 22.12. Datasheet view of the upsized qryOrderAmount query confirms that it's identical to the result set of the original Jet version.

   

Note

Access saves column widths and other datasheet properties you change in Datasheet view, but doesn't save changes you make to the sequence of columns. When you close and reopen the view, the column sequence reverts to the original sequence.

Tip

To force the datasheet column sequence to conform to that of the column list, create a stored procedure instead of a view. You can quickly create the stored procedure version of a view by copying the view's T-SQL statement, and pasting it into the SQL pane of a new stored procedure.

Dealing with Functions that Refer to Values of Access Control Objects and Other Obscure Issues

Jet queries accept values returned by Access objects, such as bound text, combo, and list boxes. For example, the Invoices Filter query uses the value of the OrdersID text box of the Orders form as the WHERE clause criterion to filter the Invoices query.

Dealing with the Invoices Filter demonstrates three important rules for testing upsized projects:

• Don't trust the Wizard Despite the known inability of the Wizard to upsize Jet queries that refer to Access object values, the Wizard attempts to do so and creates inoperable functions or stored procedures in the SQL Server database. Compare the execution behavior of every upsized query with its Jet counterpart in the source .mdb.

• Find and test each Access object that relies on an upsized query If you have a large number of application objects, this process is challenging. Take advantage of the Object Dependencies task pane to find references to the query.

  Tip

  Alternatively, export a Database Documenter report to a .rtf or .txt file, and use the Find feature of Word or your text editor to locate references to specific query names.

  
  

• Verify whether query upsizing issues are the source of apparent form or report malfunction This example demonstrates that the upsized Invoices Filter and Invoices queries require you to modify the Orders form and the associated Invoice report.

To review use of the Database Documenter, see "Generating a Data Dictionary with the Database Documenter," p. 212 .

This section addresses upsizing a specific set of objects, but the process described typifies the hurdles you face when upsizing even a relatively simple set of interdependent database and application objects.

Jet SQL

The Jet SQL statement for the Invoices Filter query (after changing DISTINCTROW to DISTINCT) is

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

T-SQL

The Upsizing Wizard treats the Forms!Orders![OrderID] element of the WHERE clause as a conventional input parameter:

 SELECT DISTINCT dbo.Invoices.* FROM dbo.Invoices WHERE (OrderID = @Forms_Orders__OrderID_) 

The Wizard replaces Access's bang (!) object separator and square bracket ([]) delimiters with underscores (_), so there are two underscores between Orders and OrderID, and another underscore following OrderID. The parameter naming problem is moot for this example, because a parameterized function won't work in the context by which it's called in the Orders form.

Spelunking Application Objects for Query References

It's not easy to determine how the Orders form employs the Invoices Filter query, because it doesn't and shouldn't appear as a value of the Filter property in the Data page of the Properties dialog for the Orders form or Orders Subform. The Print Invoice button of the Orders form executes a VBA event handler, PrintInvoice_Click, to print an invoice for the currently selected order; event-handling code calls the Invoices Filter. Clicking the Print Invoice button opens a "The column prefix 'Customers' doesn't match with [sic] a table name or alias name used in the query" message.

The reference to the Invoices Filter is in the VBA code behind the Orders form. Following is the code for PrintInvoice_Click event handler:

 Sub PrintInvoice_Click() ' This code created by Command Button Wizard. On Error GoTo Err_PrintInvoice_Click    Dim strDocName As String    strDocName = "Invoice"    ' Print Invoice report, using Invoices Filter query to print    ' invoice for current order.    DoCmd.OpenReport strDocName, acViewNormal, "Invoices Filter" Exit_PrintInvoice_Click:    Exit Sub Err_PrintInvoice_Click:    ' If action was cancelled by the user,    ' don't display an error message.    Const conErrDoCmdCancelled = 2501    If (Err = conErrDoCmdCancelled) Then       Resume Exit_PrintInvoice_Click    Else       MsgBox Err.Description       Resume Exit_PrintInvoice_Click    End If End Sub 

Note

Working with VBA code is the subject of Part VII of this book, "Programming and Converting Access Applications," so including code examples at this point might appear to be premature. If you're considering upgrading existing Access applications to SQL Server, however, it's a reasonable assumption that you have at least some familiarity with VBA.

The DoCmd.OpenReport strDocName, acViewNormal, "Invoices Filter" instruction opens the Invoice report, and applies the Invoices Filter to the report before printing. The data source for the Invoice report is the Jet Invoices query, which the Wizard has upsized to an inoperative view. Before attempting to change the event-handler code to print a single invoice, however, test the Report to determine whether it works with T-SQL view. Not surprisingly, it doesn't work, but the incorrectly upsized Invoices Filter isn't the culprit.

Correcting Duplicate Column Names in Views

To detect the source of the "column prefix" problem and correct it, do the following:

1. In the Reports page of the Database window, double-click the Invoice report. You receive the "column prefix" error message, which indicates the problem is related to the upsized Invoices query.

   Tip

   Unlike Jet queries, SQL Server views don't support table prefixes to resolve ambiguous references to columns having the same name. T-SQL doesn't permit duplication of column names, so it aliases a duplicate name with a numeric suffix.

2. Change to Report Design view, and find the controls bound to duplicate column names. The CompanyName field is common to the Customers and Shippers tables. Views don't support table names, so delete the Customers. and Shippers. prefixes from the text boxes adjacent to the Bill To and Ship Via labels.

3. Open the Invoices view in Design view to find the aliased CompanyName field, which is associated with the Shippers table. Change the CompanyName1 alias to the more descriptive ShipperName (see Figure 22.13). Save your change.

   Figure 22.13. T-SQL views require unique column names, so the Wizard generates an alias for duplicate column names in the SELECT list. Changing the CompanyName1 alias to ShipperName clarifies the view's output.

   

   Tip

   Don't confuse captions with aliases. Captions are extended properties; aliases appear in the SQL SELECT statement's field list. The Wizard has upsized Jet Caption property values to extended properties of the view, so Company Name appears as the caption for both the CompanyName and ShipperName columns. To avoid Datasheet view confusion, open the Properties dialog for the query, change the Caption property value of the column to Shipper Name, and save the change.

4. Return to the Invoice report and change the name of the CompanyName text box under the ShipVia label from CompanyName to ShipperName (see Figure 22.14).

   Figure 22.14. Changing the alias of a column of a view requires a corresponding change to the name of all application objects that are bound to the column.

   

5. Change to Print Preview view to verify that your changes work properly (see Figure 22.15). Close the Invoices report and save the changes.

   Figure 22.15. Opening the Invoice report in Print Preview demonstrates that your changes to the report and its view data source correct the initial problem.

   

6. Reopen the Orders form, if you closed it, and click the Print Invoices button. Be prepared to quickly click the Cancel button, because this operation now prints an invoice for every order.

Note

Printing every invoice instead of printing no invoice or displaying an error message is unexpected behavior. No argument value is passed as a parameter to the Invoices Filter function, so you would expect the Enter Parameter Value dialog to appear. All invoices are printed because the DoCmd.OpenReport method disregards the filter if it can't open it, instead of displaying an error message.

Removing References to the Invoices Filter from the Orders Form

Applying filters saved as query objects is a holdover from the early days of Access when macros were popular for automating applications. There's no simple method of modifying the Invoices Filter to enable its use in this scenario, and it's much more efficient to specify a filter in the VBA code that opens the Invoice report.

The syntax of the OpenReport method of the Access-specific DoCmd object is

 DoCmd.OpenReport strReportName, [intMode,    [strFilterName, [strWHERECondition,    [intWindowMode, [varOpenArguments]]]]] 

All the arguments except strReportName are optional. In this case, you delete the reference to the Invoices Filter as the strFilterName argument, and supply a strWHERECondition argument value to provide the OrderID value of the current order to the Invoices report. The strWHERECondition value is a valid WHERE clause without the WHERE keyword.

To fix the DoCmd.OpenReport statement of the PrintInvoice_Click event handler, do the following:

1. Open the Orders form in Design view, and click the Code button to open the VBA editor.

2. Scroll to the DoCmd.OpenReport... line of the PrintInvoice_Click subprocedure.

3. Delete "Invoices Filter" argument value, but not the preceding comma.

4. Add , "OrderID = " & CStr(Me.OrderID.Value) to the line (see Figure 22.16). The two commas between acViewNormal and "OrderID... are required. The CStr function converts the VariantOrderID value of the Long (Integer) type to a String variable. Me is a self-reference to the report. Use of Me and Value is optional, but is good VBA programming practice. The entire DoCmd.OpenReport statement is

    DoCmd.OpenReport strDocName, acViewNormal, ,    "OrderID = " & CStr (Me.OrderID.Value) 

   Figure 22.16. Add the highlighted code to the DoCmd.OpenReport instruction after deleting the reference to the Invoices Filter view.

   

   Tip

   If you want to display the report in Print Preview mode before printing, change the acViewNormal Access constant to acViewPreview.

5. Return to the Orders form, click Form view, and click the Print Invoice button. If you change the mode, the invoice for the selected order appears in the Print Preview window; otherwise, the default printer prints the invoice.

6. Close the Orders form and save your changes.

If you judge the amount of work required to upsize a relatively simple set of interrelated queries and application objects to be daunting, consider abandoning the upsizing process and linking SQL Server tables to your Jet application.

Upsizing Jet SQL Statements Executed by Forms, Reports, and Controls

Many Access developers use SQL SELECT statements, instead of saved (persistent) queries, to supply the record source for bound forms, reports, and controls on forms and reports. Tracking down problems with application objects that execute SQL statements directly is even more of a challenge than fixing objects that refer to SQL Server views, functions, and stored procedures. Unlike the Access 2000 version, Access 2003's Upsizing Wizard doesn't attempt to convert Jet SQL statements used as the values of Record Source and Row Source properties to SQL Server views or stored procedures.

Northwind.mdb and thus Upsize22.mdb includes an example of a form (Sales Analysis) that fails to open as a result of a Jet SQL keyword (DISTINCTROW) that T-SQL doesn't support. When you double-click the Sales Analysis form, an "Invalid SQL Statement" message opens, which advises you to "Check the server filter on the form record source." The advice is bogus because the Server Filter property value of the form is empty.

The culprits in this case are the Sales Analysis form's two alternating subforms Sales Analysis Subform1 and Sales Analysis Subform2. Both subforms have SQL statements as the value of the Record Source property. Unfortunately, the Microsoft developers added DISTINCTROW to the SELECT statement, which renders the SQL statement unusable in a project.

Tip

Don't use Jet's DISTINCTROW or T-SQL's DISTINCT qualifier unless you have a specific reason to do so. The majority of SELECT queries used as the record source for forms and reports don't return duplicate rows. You might need the DISTINCT qualifier, however, to preclude duplicate values when populating combo and list boxes from queries having joins.

To correct problems with SQL statements that serve as the record source for bound objects, do this:

1. Open the form or report in Design view, click Properties to open the Properties window for the object, and click the Data tab. For this example, open Sales Analysis Subform1 (see Figure 22.17).

   Figure 22.17. Sales Analysis Subform1 has a superfluous DISTINCTROW modifier in the SELECT statement for the form's Record Source Property. Why Microsoft's developers added this modifier is a mystery.

   

   Note

   

   Access smart tags identify each text box with a green triangle. Passing the mouse pointer over the smart tag displays a "This control has an invalid control source. The smart tag opens to provide a list of options to fix the control, but it's the form, not the control, that has Record Source property problem.

2. If the Record Source property has an SQL statement as its value, remove the DISTINCTROW modifier, if presenent, and click the Builder button to open the project designer's SQL Statement: Query window.

3. If the problem with the SQL statement isn't immediately evident, click the Check SQL Syntax button, which opens an error message that might assist in locating the errant element.

4. Use of DISTINCTROW is the obvious problem in this example, so delete it, and click the Check SQL Syntax button. After deleting DISTINCTROW, the project designer's window appears as shown in Figure 22.18.

   Figure 22.18. Clicking the Builder button of the Record Source property's text box opens the project designer's SQL Statement: Query window. The Diagram and Grid panes don't open until you click Check SQL Syntax.

   

5. Close the project designer, click Yes to save your changes, close the Properties window, and close the form or report, again saving your changes.

6. For this example, repeat step 1 for the Sales Analysis Subform2 form, delete DISTINCTROW from the SQL statement in the Record Source text box, close the Properties window and the form, and save your changes.

7. Test the form to verify that the changes you made to the SQL statements don't lead to incorrect values. Compare the data values displayed by the upsized form with that of the original Jet version. Figure 22.19 shows that deleting DISTINCTROW doesn't affect the data of the subform's PivotTable.

   Figure 22.19. The upsized PivotTable form displays data that's identical to its Jet counterpart in Upsize22.mdb or Northwind.mdb.

   

The preceding process also applies to Row Source property value of list boxes, combo boxes, and most other objects bound to SQL statements. An exception is the Row Source property of Chart objects you create with the Chart Wizard. The later "Emulating Jet Crosstab Queries with T-SQL" section shows you how to handle T-SQL's lack of crosstab query capability.