Running the Jet Crosstab Upsizer

The Visual Basic 6.0 Crosstab Upsizer program (Crosstab.exe), which you install from the \Seua11\Crosstab folder of the accompanying CD-ROM, automates generation of the view or function and stored procedure that are required to emulate a Jet crosstab query. The program connects to the .mdb file that contains the crosstab queries and the upsized SQL Server database. You must install Crosstab.exe and its associated files on a computer running Access 2003.

Note

You can learn more about the Crosstab Upsizer and download its source code from Visual Studio Magazine's "Upsize Jet Crosstab Queries" article at http://www.fawcette.com/archives/premier/mgznarch/vbpj/2001/10oct01/sqlpro0110/rj0110/rj0110-1.asp.

The help files CTHelp.doc and CTHelp.txt provide detailed descriptions for using the Crosstab Upsizer, so the following is a brief example of its use with the Upsize22.mdb file and UpsizeSQL database.

To install and test-drive the Crosstab Upsizer, do the following:

1. Run Setup.exe from your \Program Files\Seua11\Crosstabs folder to install the required files and Programs menu shortcuts. By default, the program installs in the \Program Files\CrosstabUpsizer folder.

2. Choose Program Files, Crosstab Upsizer, Crosstab Upsizer to open the logon dialog. Type the path to the .mdb file or click the ... button to browse for the .mdb file. The default is C:\Program Files\Seua11\Chaptr22\Upsize22.mdb.

3. If your .mdb file is secure, specify the workgroup file in the SystemDB text box or browse for it. Type your user name and password for the .mdb file.

4. Accept the (LOCAL) default or type the SQL Server name in the Instance text box and specify the upsized database name. By default, the program uses Windows security to connect to the database (see Figure 22.32).

   Figure 22.32. Specify the source .mdb file and your Jet credentials, and the SQL Server instance and database in the login dialog.

   

5. Click Connect to connect to the .mdb file and database and open the main Upsizer form with a list of the crosstab queries in the .mdb file. Click one of the queries in the list to display its SQL statement in the text box and result set in the grid (see Figure 22.33). If the crosstab query requires parameters, an input box requests parameter value(s). (Use 1996, 1997, or 1998 for the qryQuarterlyOrdersByProductParam query.)

   Figure 22.33. Selecting a crosstab query in the list retrieves its SQL statement, executes the query, and displays the data in a grid.

   

6. Click Generate T-SQL for the View to write the SQL statement for the aggregate values query. If the query has parameters, the Upsizer generates an in-line, table valued function.

   Tip

   If your crosstab query doesn't include a CCur() function for currency, wrap the view's value field expression with a T-SQL CONVERT(money, expression) function.

7. Click Create/Alter and Execute the View to display the query result set in the grid. Mark Cents for Money if you want decimal currency values, and re-execute the query (see Figure 22.34).

   Figure 22.34. The intermediate view or function you generate provides the aggregate values for the stored procedure.

   

8. Click Generate T-SQL for the Table SP to display the proposed SQL statement to generate the table from the view result set. If you want to include column totals, row totals, or both, mark the check boxes and click Generate again. The grid displays properties of the table fields (see Figure 22.35).

   Figure 22.35. The table that stores the crosstab data is named for the view that creates it.

   

9. Click Create/Alter and Execute the SP to display the crosstab results in the grid. The values duplicate those of Figure 22.33, and include row and column (crossfoot) totals (see Figure 22.36).

   Figure 22.36. The T-SQL stored procedure emulates the Jet crosstab query and includes optional row and column totals.

   

The Crosstab Upsizer can't process every Jet crosstab query automatically, so you might need to edit the T-SQL statements for the view/function or stored procedure to obtain the desired result.

Caution

If you uninstall the Crosstab Upsizer, you might receive a "Shared File" message. If you see this message do not remove any shared files.