|
 rosstab queries have been a primary selling point for Access since Microsoft released version 1.0 in 1993. A multitude of Visual Basic developers depend on Access' Crosstab Query Wizard to create persistent QueryDef objects or as a helper for generating Jet crosstab SQL statements with code. Access' Chart Wizard generates a crosstab query as the data source for charts and graphs. Despite the popularity of Jet crosstabs, the SQL Server team implemented the non-ANSI and seldom-used ROLLUP and CUBE operators, but not PIVOT and TRANSFORMboth are necessary for generating crosstabs.
 |
What you need:
VB6 SP5+, SQL Server 2000 or MSDE 2000, Jet 3.6+ MDB file(s) with crosstab queries, Access 2002 recommended for upsizing Jet tables and most QueryDef objects to SQL Server/MSDE 2000 |
 |
|
The most likely explanation for these omissions is Microsoft's promotion of Office Web Components (OWC)PivotTables and PivotChartsas replacements for crosstab queries and the Microsoft Graph OLE server. You can download Office XP's OWC 10.0 from the Microsoft Web site, but the PivotTable and PivotChart controls run in limited-functionality mode unless your applications' users have Office XP licenses.
Whatever the reason for the lack of PIVOT and TRANSFORM operators in Transact-SQL (T-SQL), the upshot is that all versions of the Access Upsizing Wizard refuse to upsize Jet crosstab queries. So Access forms and reports with embedded Microsoft Graph objects drop dead when imported to Access Data Projects (ADPs). VB developers migrating apps from Jet to SQL Server or other client/server RDBMSs face the daunting prospect of writing heavy-duty custom code to emulate PIVOT and TRANSFORM operations on traditional, normalized Recordset objects.
I've written example crosstab emulation code and put it to work in the Crosstab.vbp project (download it here). The project uses a wizard-like approach to automate the crosstab upsizing process. I'll walk you through some of the project's inner workings, showing you how to migrate crosstab queries to SQL Server and warning you about gotchas. But first let me comment on some of the documentation and give you some background on upsizing Jet crosstab queries.
The SQL Server 2000 Resource Kit's Chapter 5, "Migrating Access 2000 Databases to SQL Server 2000," suggests that "[a]n Access crosstab query can be implemented as a Transact-SQL SELECT statement in [a] SQL script, a stored procedure, or a view," but it gives no examples for accomplishing such feats (see Resources). Don't hold your breath waiting for the Resource Kit's authors to demonstrate how to implement a Jet crosstab query as a single SELECT statement, stored procedure, or view in a Knowledge Base or TechNet article. I found the cross-tab upsizing process considerably more complex than the Resource Kit implies. Upsizing parameterized crosstab queries, in particular, is challenging.
|
Upsize Crosstabs