crosstab.frm

《VB6数据库开发指南》所有的例程的源码

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Begin VB.Form Form1 
   Caption         =   "Chapter 3.16 Example"
   ClientHeight    =   4155
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   6135
   LinkTopic       =   "Form1"
   ScaleHeight     =   4155
   ScaleWidth      =   6135
   StartUpPosition =   3  'Windows Default
   Begin VB.Data dtaData 
      Caption         =   "dtaData"
      Connect         =   "Access"
      DatabaseName    =   ""
      DefaultCursorType=   0  'DefaultCursor
      DefaultType     =   2  'UseODBC
      Exclusive       =   0   'False
      Height          =   300
      Left            =   90
      Options         =   0
      ReadOnly        =   0   'False
      RecordsetType   =   1  'Dynaset
      RecordSource    =   ""
      Top             =   3780
      Width           =   1185
   End
   Begin VB.CommandButton cmdClose 
      Caption         =   "&Close"
      Height          =   525
      Left            =   4800
      TabIndex        =   2
      Top             =   3540
      Width           =   1245
   End
   Begin MSFlexGridLib.MSFlexGrid grdCrossTab 
      Bindings        =   "Crosstab.frx":0000
      Height          =   3075
      Left            =   60
      TabIndex        =   1
      Top             =   330
      Width           =   5985
      _ExtentX        =   10557
      _ExtentY        =   5424
      _Version        =   327680
      FixedCols       =   0
      AllowUserResizing=   1
   End
   Begin VB.Label lblCrossTab 
      Caption         =   "Titles per year published after 1975, sorted by Publisher ID"
      Height          =   315
      Left            =   90
      TabIndex        =   0
      Top             =   60
      Width           =   5925
   End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Private Const BIBLIO_PATH = "D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"

Private Sub cmdClose_Click()
    End
End Sub

Private Sub Form_Load()
    Dim strSQL As String
    
    'Construct the crosstab query statement.  Note the use of several new
    'SQL keywords, including TRANSFORM and PIVOT.  These two keywords are the
    'building blocks of the crosstab query.
    strSQL = "TRANSFORM Count(Titles.Title) AS [TitlesCount] " & _
        "SELECT Publishers.Name FROM Publishers INNER JOIN Titles ON (Titles.PubID " & _
        "= Publishers.PubID) WHERE Titles.[Year Published] > 1975 " & _
        "GROUP BY Publishers.Name " & _
        "PIVOT Titles.[Year Published]"
        
    'Set up the data control
    With dtaData
        .DatabaseName = BIBLIO_PATH
        .RecordSource = strSQL
        .Refresh
    End With
        
End Sub