Converting Your Combo Box Form to an Access Data Project

Access 2003 and 2002's Upsizing Wizard (AUW) does a much better job of converting Jet applications to Access data projects (ADP) than the Access 2000 version. In most cases, however, the AUW can't upsize complex forms that are operable in ADP. For instance, the AUW doesn't translate to Transact-SQL (T-SQL), the Jet-specific SQL statements that serve as the Record Source property of bound forms and reports, and the Row Source property value of controls. The forms you created in the preceding sections provide good examples of the problems you encounter when upsizing forms to ADP.

For an explanation of some of the limitations of the AUW, see "Upsizing with the Trial and Error Approach," p. 889.

Upsizing VBACombo.mdb isn't practical, so the sections that follow show you how to manually upsize forms imported to the project from VBACombo.mdb. You can't import Jet queries to ADP, so you create SQL Server views based on the Jet SQL of VBACombo.mdb's queries.

Note

The NorthwindCS SQL Server database for NorthwindCS.adp must be installed to execute the code in the following example.

If you created the copy of the NorthwindCS database as Northwind (or any other name) for use with the AddOrders.adp project described in Chapter 30, "Understanding Universal Data Access, OLE DB, and ADO," you can use AddOrders.adp to add a large number of records to the Orders and Order Details tables to test the performance of combo and list boxes with databases that are more representative of production applications.

Note

If you haven't created NorthwindCS, see "Exploring the NorthwindCS Sample Project," p. 808.

To use AddOrders.adp with a copy of NorthwindCS, see "Exploring the AddOrder.adp Sample Project," p. 1327.

Importing and Testing the Combo Box Forms

To import the three forms you created in the preceding sections to an .adp file and test them against a local instance of MSDE, do the following:

1. Start MSDE with SQL Service Manager, if MSDE isn't running.

2. Open the task pane and double-click Project (Existing Data) to open the File New Database dialog.

3. Type VBACombo.adp as the File Name, and click Create to close the dialog and open the Data Link Properties dialog.

4. Type (local) or your computer for the Server Name, and select the Use Windows NT Integrated Security option.

5. Select NorthwindCS from the drop-down list, and click the Test Connection button to check the Data Link properties you specified (see Figure 29.14).

   Figure 29.14. Establish a connection to the NorthwindCS sample database on your machine.

   

6. Click OK twice to return to the Database window for the new project.

7. Choose File, Get External Data, Import to open the Import dialog. (You can import the three forms from the sample file, VBACombo.mdb or your working copy of Northwind.mdb, if you created those forms earlier.) Click the Forms tab and click Select All if you're importing from VBACombo.mdb. If you're importing from Northwind, select the three forms frmCombo1, frmDrillDown, and frmDrillDownAll and then click OK to import the three forms to the project.

8. Open the frmCombo1 form. The Country and Product combo boxes work, but no records appear in the Orders list box, despite the fact that combo box selections generate the correct label caption text. (Populating the Orders list box depends on the missing qryCombo1 query.) Close the form.

9. Open the frmDrillDown form. You receive two error messages stating that the Jet qryCombo1 and qryDrillDown queries don't exist. Close the form.

Replacing the Jet qryCombo1 Query with an SQL Server View

One approach to upsizing Row Source property values from Jet queries to SQL Server databases is to substitute T-SQL statements for the queries. A simpler method is to create SQL Server 2000 views that duplicate the Jet queries.

To create a view to replace qryCombo1, do this:

1. Open another instance of Access 2003, open VBACombo.mdb or Northwind.mdb (if you created the forms earlier), and open qryCombo1.

2. Select SQL View to open the SQL window, select the SQL statement, and press Ctrl+C to copy the text to the Clipboard.

3. Return to the project, select Queries in the Database window, and double-click the Create View in Designer item to open the project designer.

4. Close the Add Table dialog, and click the SQL button to open the designer's SQL pane.

5. Select the SELECT and FROM lines, press Ctrl+V to replace the lines with the qryCombo1 SQL statement, and delete the trailing semicolon.

6. Change the Orders.ShippedDate field list item to Orders.OrderDate. (AddOrders.adp doesn't add ShippedDate values.)

7. Click the Run button, save your view as vwCombo1, and verify that the query executes correctly. Figure 29.15 shows the design of the view.

   Figure 29.15. This SQL Server view replaces the Jet qryCombo1 query in the three imported forms of VBACombo.adp.

   

8. Select frmCombo1 in the Database window and click the Code button to open the VBA editor. Replace qryCombo1 with vwCombo1, ShippedDate with OrderDate in the declaration of strSQL1, and ShippedDate with OrderDate in strSQL3 (see Figure 29.16).

   Figure 29.16. Change qryCombo1 to vwCombo1 and ShippedDate to OrderDate in the Class Module for each form.

   

9. Change to Form view, test the form with a few combinations of Country and Product selections, close the form, and save your changes.

10. Repeat steps 8 and 9 for the frmDrillDown and frmDrillDownAll forms. In frmDrillDownAll, also change Orders.ShippedDate to Orders.OrderDate in the declaration of strSQL5. Acknowledge the error messages when you open frmDrillDown for testing. (The lstLineItems list isn't operable at this point.)

Conforming the Jet SQL of qryDrillDown to T-SQL Syntax

The Jet SQL statement for qryDrillDown includes the VBA CCur and Format functions that T-SQL doesn't support, and has the Percent format applied to the Discount column. Thus, you must substitute the T-SQL CONVERT function for the CCur and Format combination. The CONVERT function and an added % symbol solve the formatting problem with the Discount column.

For another example of using the CONVERT function, see "Exploring SQL Server Views," p. 819.

To create a view that emulates the formatting of the original Jet query, do the following:

1. Copy the Jet SQL statement for the qryDrillDown query to a new SQL Server view by the method described in steps 2 5 of the preceding section.

2. Click Run, save the query as vwDrillDown, and acknowledge the error message caused by the VBA functions in qryDrillDown's SQL statement.

3. Replace the CCur(Format(...)) AS Extended line with the following:

    (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount) AS Extended 

4. Run the query again to check your initial changes to conform to T-SQL syntax.

5. Return to Design view, and edit the line you changed in step 3 to add the CONVERT function as follows:

    CONVERT(money, (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)) AS Extended 

6. Rerun the query to check your change. The Extended column now has currency formatting.

7. In Design view, replace dbo.[Order Details].Discount, with this expression to multiply by 100, add two decimal places, convert to a string, and add the % symbol:

    CONVERT(varchar, CONVERT(decimal(5,2), dbo.[Order Details].Discount * 100))        + '%' AS Discount, 

8. Run the query again to verify the format changes (see Figure 29.17, which illustrates the effect of inflation on the price of Alice Mutton). Figure 29.18 shows the query design.

   Figure 29.17. The result set of the modified vwDrillDown view duplicates the formatting of the Jet qryDrillDown query. The Discount column is left-justified because Discount has the varchar datatype.

   

   Figure 29.18. The Design view of vwDrillDown has been modified here to improve readability of the T-SQL statements.

   

9. Close the view, select frmDrillDown in the Database window, and click the Code button to open the VBA editor.

10. In the Declarations section, change qryDrillDown to vwDrillDown in the declaration of strSQL4, and save your change.

11. In Form Design view, select lstOrders, open the Properties window, click the Data tab, and delete the persisted Row Source value, if present. Select lstLineItems, and delete its Row Source value.

12. Run the form and verify your changes.

13. Repeat steps 9 12 for the frmDrillDownAll form. When testing this form, verify that (All) products and (All) countries deliver the expected result.

Note

The VBACombo.adp sample project has two versions of each form. Form names with a (View) suffix are intended to operate with two SQL Server views that are equivalent to the queries you created in this and the preceding section. Forms without the suffix send T-SQL statements to the server, so you don't need to create the views to test these forms.

You can use Chapter 30's AddOrders.adp to add a few thousand new orders to the Orders and Order Details tables of a copy of NorthwindCS and connect to the copy, the time to fill lstOrders increases (as expected). The time required to retrieve the line items for an order, however, doesn't increase perceptively. Figure 29.19 shows the result of a frmDrillDownAll selection against tables with 10,830 orders and 113,865 line items. The sample forms without the suffix have two added features when you click lstOrders, the code adds the number of orders in lstOrders to lblList's caption and adds the total amount of the order to lblLineItems' caption.

Figure 29.19. The sample frmDrillDownAll form displays a few of the 2,021 orders from the USA for a database with 10,830 orders added by the AddOrders.adp sample project.

Tip

If your list-based application runs against tables with a large number of rows, consider adding a WHERE clause criterion to eliminate unneeded rows. For this example, you might add a date-based criterion to limit the Orders query to OrderDate values within the last year, quarter, or month. Alternatively, add a TOP 500 or TOP 25 PERCENT modifier to your select statement.