In an earlier post I looked at the way in which it is possible to open up a Crystal Report from VB or VBA code in The Raiser’s Edge. That was with version 8.5 of Crystal Reports. I tried to do the same thing recently with a report created in Crystal Reports 11 and the code broke. I rewrote the code using a newer object and technique.

I wrote the code as a VBA macro and that was then placed on the user’s home page as a link. Sometimes it is not possible to create a report using export and custom reports. Export does not allow all permutations of data to be exported so it may be neccessary to query the database directly using a Crystal Report.

Public Const ODBCSOURCE As String = "RE7_1"
Public Const REPORT_LOC As String = "C:\Reports\Report.rpt"
Public Const DB As String = "RE7_1"

Public Sub GenerateLocalRepReport()


    Dim crystalApp As New CRAXDRT.Application
    Dim crxTable As CRAXDRT.DatabaseTable
    Dim crystalReport As CRAXDRT.Report
    
    Set crystalReport = crystalApp.OpenReport(REPORT_LOC)
    crystalReport.DiscardSavedData
    
    ' Set the DSN connection for every table in your database
    On Error GoTo BadCrystalConnection
    For Each crxTable In crystalReport.Database.Tables
        crxTable.ConnectionProperties.DeleteAll
        crxTable.ConnectionProperties.Add "DSN", ODBCSOURCE
        crxTable.ConnectionProperties.Add "Database", DB 
        crxTable.ConnectionProperties.Add "User ID", "MySQLUserId" 
        crxTable.ConnectionProperties.Add "Password", "PasswordForMySQLUserId" 
        
        'Unsure why this is required but without it there is an error message saying that tables cannot be found
        crxTable.Location = crxTable.Location

    Next crxTable
    On Error GoTo 0
           
    crystalReport.Export
    
    Exit Sub
    
BadCrystalConnection:
MsgBox "The DSN connection string for Crystal Reports is invalid"
    
End Sub

This code will not show the Crystal Report but it does allow you to export it to pdf or any other format. Also if you have any sub reports in the report then this will not work. I’ll post a follow up soon showing how to do that…