VBGood网站全文搜索 Google

搜索VBGood全站网页(全文搜索)
首页 - 经验之谈 - 可以在程序中动态建立临时表
发表评论(0)作者:不详, 平台:VB6.0+Win98, 阅读:13498, 日期:2001-01-17
问题:有两个表(tbl1,tbl2),其中有一个字段内容相同,为bm,我现在想提取出两个表中bm都为某个值(比如说bm=1062)的记录,合成一个新表,作为我报表设计器的数据来源,怎么做?最好能给出代码,拜托了!

回答:可以在程序中动态建立临时表
在报表设计器的Initialize事件中:

  Dim rs As New Recordset
  Dim cmd As New Command

  Dim strCNN, strSql As String

    `连接数据库
    ..............
    
    `创建临时数据表
    strSql = "CREATE TABLE tale_name([bm] TEXT(6),[mc] TEXT(10))"
    cn.Execute strSql

    `由tbl1,tbl2中读取数据并写入临时表中。
    ..............

    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        .CommandText = "Select bm,mc from table_name"
        .Execute
    End With
   
    With rs
        .ActiveConnection = cn
        .CursorLocation = adUseClient
        .Open cmd
    End With
   
    q = 0
    z = 0
    With Data_Rpt
        .Hide
        
        `报表表头设计
        With .Sections("PageHeader")
            .Controls("lab_date").Caption = strDate
            .Controls("lab_Brhbtn").Caption = "01"
            .Controls("lab_cusbrh").Caption = "101001"
            .Controls("lab_trxcur").Caption = "01"
            .Controls("lab_actact").Caption = "100"
        End With
        
        .Sections("ReportFooter").Controls("lab_count").Caption = "总人数:" & CStr(RecNumber)
        
        Set .DataSource = rs
        .DataMember = cmd.Name
        
        `报表明细设计
        With .Sections("tbl_pgmp_Detail").Controls
            For intCtrl = 1 To .Count
                If TypeOf .Item(intCtrl) Is RptLabel Then
                    .Item(intCtrl).Caption = rs.Fields(q).Name & " :"
                    q = q + 1
                End If
                If TypeOf .Item(intCtrl) Is RptTextBox Then
                    .Item(intCtrl).DataMember = cmd.Name
                    .Item(intCtrl).DataField = rs(z).Name
                    z = z + 1
                End If
            Next intCtrl
        End With
        .Refresh
        .Show
    End With

在报表设计器的Terminate事件中:

    `清除临时数据表
    cn.Execute "DROP TABLE table_name"
    cn.Close