How to use ADO to create a simple report on a feature class

This example demonstrates how use Microsoft® ActiveX® Data Objects, ArcObjects and Seagate Crystal Reports™ 8 (Developer Edition) to create a simple report on information stored in a feature class.

How to use

  1. Paste the code into your VB or VBA application.
  2. In addition to the ESRI Object Library, the VB/VBA application must also reference both the Microsoft ActiveX Data Objects Library and the Crystal Report 8 ActiveX Designer Run time Library (available with Crystal Reports 8 Developer Edition).
  3. Call the procedure from within your application.
[VBA] 
Sub ADO_report(pWkspace As IWorkspace)
    '++ ADO_report: Creates a report on a feature class.
    '++ Accepts a workspace object as an argument
    '++ to create an ADO connection
    
On Error GoTo Report_fail:
    
    '++ ADO Objects
    Dim pAdoCon As ADODB.Connection
    Dim pAdors As ADODB.Recordset
    
    '++ FDOADOConnection object
    Dim pFdoCon As IFDOToADOConnection
    Set pFdoCon = New FdoAdoConnection
    
    '++ Create a NEW ADO Connection object from the workspace
    Set pAdoCon = pFdoCon.CreateADOConnection(pWkspace)
    
    '++ Crystal Report Objects
    Dim pProj As CRAXDRT.Application
    Dim pReport As CRAXDRT.Report
    
    Set pProj = New CRAXDRT.Application
    Set pReport = pProj.NewReport
    
    '++ Create and open a recordset
    Dim SQLStr As String
    SQLStr = "select STATE_NAME, STATE_FIPS, SUB_REGION from gdb.us_states "
    Set pAdors = New ADODB.Recordset
    
    '++ Uncomment the next line if working with VB and an Access workspace
    '++ pAdors.cursorlocation = adUseClient
    pAdors.Open SQLStr, pAdoCon, adOpenForwardOnly, adLockOptimistic
    
    '++ The pLocation parameter is set to an empty string
    '++ The pConnectInfo parameter is set to the ADO recordset
    '++ The pDLLName parameter is set to the Crystal Active Data Driver (P2smon.dll)
    
    pReport.Database.Tables.Add "", , pAdors, , "p2smon.dll"
    pReport.LeftMargin = 2500
    
    '++ AddDetail - adds detail to the report
    Call AddDetail(pReport)
    
    '++ Send to (default) printer, don't prompt user
    pReport.PrintOut False, 1, , 1
    
    pAdors.Close
    Set pAdors = Nothing
    pAdoCon.Close
    Set pAdoCon = Nothing
    
    Exit Sub
Report_fail:
    MsgBox "ADO_report module : " & Err.Number, Err.Description, vbInformation
    
End Sub

Private Sub AddDetail(pRpt As Report)
    '++ AddDetail: Formats the report
On Error GoTo Detail_fail:
    
    '++ Create the line/text/field objects for report
    Dim ln1Obj As LineObject
    Dim ln2obj As LineObject
    Dim ln3Obj As LineObject
    Dim ln4Obj As LineObject
    Dim ln5Obj As LineObject
    Dim ln6Obj As LineObject
    
    Dim txt1Obj As TextObject
    Dim txt2Obj As TextObject
    Dim txt3Obj As TextObject
    
    Dim fld1Obj As FieldObject
    Dim fld2Obj As FieldObject
    Dim fld3Obj As FieldObject
    
    pRpt.ReportTitle = "ADO / Crystal Reports Sample"
    
    '++ Report header section
    With pRpt.Sections(1)
        .AddSpecialVarFieldObject crSVTReportTitle, 2300, 100
    End With
    
    '++ Page header section
    With pRpt.Sections(2)
        Set txt1Obj = .AddTextObject("State Name", 0, 420)
        txt1Obj.Font.Bold = True
        txt1Obj.Font.Size = 9
        Set txt2Obj = .AddTextObject("Sub Region Code", 2500, 420)
        txt2Obj.Font.Bold = True
        txt2Obj.Font.Size = 9
        Set txt3Obj = .AddTextObject("State Fips Code", 5000, 420)
        txt3Obj.Font.Bold = True
        txt3Obj.Font.Size = 9
    End With
    
    '++ Details section
    With pRpt.Sections(3)
        '++ Add three field objects to report
        '++ Table data source is always called "ado"
        Set fld1Obj = .AddFieldObject("{ado.STATE_NAME}", 500, 0)
        Set fld2Obj = .AddFieldObject("{ado.SUB_REGION}", 3000, 0)
        Set fld2Obj = .AddFieldObject("{ado.SUB_REGION}", 5500, 0)
        
        '++ Add some lines for clarity
        Set ln1Obj = .AddLineObject(2500, 0, 2500, 10)
        ln1Obj.LineThickness = 2
        ln1Obj.ExtendToBottomOfSection = True
        Set ln2obj = .AddLineObject(5000, 0, 5000, 10)
        ln2obj.LineThickness = 2
        ln2obj.ExtendToBottomOfSection = True
        Set ln3Obj = .AddLineObject(0, 10, 6500, 10)
        ln3Obj.LineThickness = 2
        Set ln5Obj = .AddLineObject(0, 0, 0, 10)
        ln5Obj.LineThickness = 2
        ln5Obj.ExtendToBottomOfSection = True
        Set ln6Obj = .AddLineObject(6500, 0, 6500, 10)
        ln6Obj.LineThickness = 2
        ln6Obj.ExtendToBottomOfSection = True
    End With
    
    '++ Report footer section
    With pRpt.Sections.Item(4)
        Set ln4Obj = .AddLineObject(0, 0, 6500, 0)
        ln4Obj.LineThickness = 2
    End With
    
    '++ Page footer section
    With pRpt.Sections.Item(5)
        .AddSpecialVarFieldObject crSVTDataDate, 30, 0
        .AddSpecialVarFieldObject crSVTDataTime, 10, 200
    End With
    
    Exit Sub
Detail_fail:
    MsgBox "AddDetailObjects  :" & Err.Number, Err.Description, vbInformation
End Sub





Additional Requirements
  • Crystal Reports 8 (Run time)