Hi all. Thank you for taking a look at this. I apologize for the sheer size of the post. I tried to keep it as short as I could. I inherited a legacy report built using CR9. An application written in VB6 calls it. Our back end is SQL Server 2005. I should note that I have no experience with Crystal Reports.

The report uses a subreport. Both reports have server and database information hardcoded in them.

Both reports have a database, tables and fields defined from within. Additionally, the VB6 code passes an ADO record set to the main report. Since the subreport displays the details, I assume that the record set is passed from the app to the main report and then to the subreport.

The subreport is explicitly referenced within the RPT file for the main report. The code below shows a line that explicitly sets the subreport, but I don’t know how necessary it is to do this since the subreport name is referenced within the RPT file.

The code that is being used to call the report is the following:
Code:
  Dim sSQL As String
  Dim crApp As New CRAXDRT.Application
  Dim crReport As New CRAXDRT.Report
  Dim crReportSub As New CRAXDRT.Report

  Dim rsRpt As New ADODB.Recordset

  Set crReport = crApp.OpenReport(strRPTPath)  ‘Full path where RPT file is located

  'Create the recordset that will be passed to the report.
  sSQL = "select * from RepTemptable"
      
  ‘Call function to create the record set given the query.
  Set rsRpt = fnGetADORS(sSQL)

  crReport.Database.Tables(1).SetDataSource rsRpt, 3

  ‘Full path of the sub report
  Set crReportSub = crReport.OpenSubreport("SubReportDet.rpt")

  'Send to screen.
  ‘
  ‘ fCR9 is form where CRViewer 9 object oCRV9 is located.
  fCR9.Visible = False
  fCR9.oCRV9.ReportSource = crReport
            
  fCR9.oCRV9.ViewReport
  fCR9.oCRV9.EnableGroupTree = False
        
  'The report stays onscreen until the user exits,
  fCR9.Show vbModal

  Set fCR9 = Nothing
This report was broken when the database was moved to another server (actually two servers, same database, different data). So far I’ve done the following:

  1. With the original report I tried to call the report it gave me the error “database not found”. This was due to the change in severs. I managed to define the new server name and updated the preexisting server name. I ran the report within the CR (standalone) Designer and it and the subreport looked ok (did not verify data, but that is in its own time).
  2. I ran the app and called the modified report, but now I am getting Logon failed for user ‘AppDBUsr’. It seems that the password is required. I added the following code, but this did not fix anything, remains the same:

    Code:
    crReport.Database.Tables(1).ConnectionProperties.Add "Connection String", _
         "Driver=SQLOLEDB;Data Source=CORPDATASQL1;” _
         “Initial Catalog=MainDB;” _
         “User ID=AppDBUsr;” _
         “Password=****;” _
         “Trusted_Connection=yes"
  3. I also attempted to remove the hardcoded database references within the RPT report file, but I get a message indicating that certain fields within that data source are being used.


My goal is to eliminate these completely so that the calling app can set these since the server and database names might vary (although the tables and fields within the DB remain the same)

The fields that the report references within itself are used as Group Headers (4 of them).

What I can’t seem to figure out:

  1. How is the ADO record set that is passed to the report referenced within the report or subreport if that is the case?
  2. How can I eliminate the 4 fields that are referenced within the report as Group Header and instead use data from a record set that I pass to the report?


I’ve done research and found lots of Crystal Reports information, but most is for versions other than 9. Some videos on You Tube look useful, but from a general point of view. Since I lack the skills at this time I am willing to do my homework and follow any orientation or suggestions given here.

Thank you.
Salvador