Crystal Reports Access through VB6 - Subreports

[hgraybill]

I am trying to call a Crystal Report (CR10) that has a subreport (the subreport connects to a different database than the main report) through my VB6 program. I usually don't have a problem calling reports with my code, but I've never done a report that has a subreport that connects to a different database (username and password is the same for the second database. The only thing different is the database name).

I found the code below in the FAQ's here, and after changing the server, database name, password, etc to match mine, I still cannot get it to work.

Any help is appreciated!

--------------------------------------
Dim Report As CRAXDRT.Report
Dim SubReport As CRAXDRT.Report
Dim App As CRAXDRT.Application
Dim Sections As CRAXDRT.Sections
Dim Section As CRAXDRT.Section
Dim RepObjs As CRAXDRT.ReportObjects
Dim SubReportObj As CRAXDRT.SubreportObject
Dim n As Integer
Dim i As Integer
Dim j As Integer

Set App = New CRAXDRT.Application
Set Report = App.OpenReport("your.rpt")

For n = 1 To Report.Database.Tables.Count
Report.Database.Tables.SetLogOnInfo "server", "dbname", "user", "pass"
Next n

Set Sections = Report.Sections
For n = 1 To Sections.Count
Set Section = Sections.Item
Set RepObjs = Section.ReportObjects
For i = 1 To RepObjs.Count
If RepObjs.Item(i).Kind = crSubreportObject Then
Set SubReportObj = RepObjs.Item(i)
Set SubReport = SubReportObj.OpenSubreport
For j = 1 To SubReport.Database.Tables.Count
SubReport.Database.Tables(j).SetLogOnInfo "server", "db", "username", "pass"
Next j
End If
Next i
Next n

 

[hgraybill]

I forgot to include my code correctly and didn't see a way to edit the post

Dim Report As CRAXDRT.Report
Dim SubReport As CRAXDRT.Report
Dim App As CRAXDRT.Application
Dim Sections As CRAXDRT.Sections
Dim Section As CRAXDRT.Section
Dim RepObjs As CRAXDRT.ReportObjects
Dim SubReportObj As CRAXDRT.SubreportObject
Dim n As Integer
Dim i As Integer
Dim j As Integer

  Set App = New CRAXDRT.Application
  Set Report = App.OpenReport("your.rpt")

  For n = 1 To Report.Database.Tables.Count
    Report.Database.Tables(n).SetLogOnInfo "server", "dbname", "user", "pass"
  Next n

  Set Sections = Report.Sections
  For n = 1 To Sections.Count
    Set Section = Sections.Item(n)
    Set RepObjs = Section.ReportObjects
    For i = 1 To RepObjs.Count
      If RepObjs.Item(i).Kind = crSubreportObject Then
         Set SubReportObj = RepObjs.Item(i)
         Set SubReport = SubReportObj.OpenSubreport
         For j = 1 To SubReport.Database.Tables.Count
            SubReport.Database.Tables(j).SetLogOnInfo "server", "db", "username", "pass"
         Next j
      End If
    Next i
  Next n

 

[TLowder]

You might want to try the Crystal forum if no one here knows. I've never done a subreport from another database. Perhaps it can't be done, I don't know. But a workaround might be to insert the subreport data into temporary tables in the main database before the report is loaded.

Tom

 

[hgraybill]

Thanks for the response! I will try the Crystal Reports forum to see if anyone knows the answer.

If not, I'll give the temp tables a try!

 

[TLowder]

Wait a minute. Have you ever loaded a subreport even from the same database? My code is different than yours and after having a look at my code, the sub report has its own connection string. While I've never done it from a different database it very well should be possible. If you've never done a subreport I'll give you a sample of mine and see if it works for you.


Tom

 

[hgraybill]

Yes, I've used subreports in program code before and had them come up fine in other programs. I don't recall ever having to use a separate connection string, though. Maybe your sample would help me.

I can show a sample of my code that has successfully used subreports from the same database, too, if you'd like to see it.

 

[TLowder]

Here is how I load subreports. This sub can load multiple subreports. I also pass parameter fields so call like sample.

Call LoadRptMultiSub("Your.rpt", Mainsql, False, sqlSub, SubName, xx(), 0, 2)

Sub LoadRptMultiSub(Rpt As String, sql As String, autoprint As Boolean, SubSql() As String, SubReportName() As String, PV() As String, PC As Long, SubNum As Integer)
'''''Cut for sample - never tested

Dim CRApp As New CRAXDRT.Application
Dim crReport As New CRAXDRT.Report
Dim crSubReport As New CRAXDRT.Report
Dim SubX As Integer

Dim X As Long


Dim adors As ADODB.Recordset
Dim adors2 As ADODB.Recordset


  frmReport.CRViewer1.Zoom 100
  
      
  Set crReport = CRApp.OpenReport("your.rpt")
  
  Set adors = New ADODB.Recordset
  Set adors2 = New ADODB.Recordset
  

  adors.CursorLocation = adUseClient
  adors2.CursorLocation = adUseClient

  adors.Open sql, GCon, adOpenForwardOnly, adLockOptimistic

  crReport.Database.SetDataSource adors
  
  
  For SubX = 0 To SubNum - 1
  
    adors2.Open SubSql(SubX), GCon, adOpenForwardOnly, adLockOptimistic
    Set crSubReport = crReport.OpenSubreport(Mid(SubReportName(SubX), 1, Len(SubReportName(SubX)) - 4))
    
    crSubReport.Database.SetDataSource adors2
    
    For X = 0 To PC - 1
      crReport.ParameterFields(X + 1).AddCurrentValue PV(X)
    Next X
    
    crSubReport.ReadRecords
    adors2.Close
  Next SubX

  crReport.ReadRecords
  

  adors.Close
  

Set adors = Nothing



If autoprint = True Then
  crReport.PrintOut (False)

  
Else
  frmReport.CRViewer1.ReportSource = crReport
  frmReport.CRViewer1.ViewReport
  frmReport.Show vbModal
End If
Exit Sub

Unload frmReport

 
End Sub

Tom

 

[hgraybill]

Thank you so much! I will give it a try.

 

[TLowder]

Gcon is my connection string just put in yours and make the 2nd one different. I haven't tested this for a different database but give it a try.

Tom

 

[Golom]

Here's what I use

Dim rdApp                       As CRAXDRT.Application
Dim rpt                         As CRAXDRT.Report
Dim tbl                         As CRAXDRT.DatabaseTable
Dim SubReportObj                As CRAXDRT.SubreportObject
Dim SubReport                   As CRAXDRT.Report
Dim Section                     As CRAXDRT.Section
Dim RObject                     As Object

Set rdApp = CreateObject("CrystalRuntime.Application")
Set rpt = rdApp.OpenReport(Rep_Path & Rep_Name)

[COLOR=black cyan]' Reset the database paths in subReports[/color]
For Each Section In rpt.Sections
    For Each RObject In Section.ReportObjects
        If RObject.Kind = crSubreportObject Then
            Set SubReportObj = RObject
            Set SubReport = SubReportObj.OpenSubreport

            For Each tbl In SubReport.Database.Tables

                [COLOR=black cyan]' Direct Access Database[/color]
                tbl.Location = [red]Data_Path & Mid$(tbl.Location, InStrRev(tbl.Location, "\") + 1)[/red]
            Next tbl
        End If
    Next RObject
Next Section
Set SubReportObj = Nothing
Set SubReport = Nothing

The above is somewhat simplified. The unabridged code handles thinks like ODBC data sources and tables from multiple sources in the same sub-report.

In the place marked in [red]red[/red] you can substitute the database information for the database to be used in the sub-report.

 

[hgraybill]

Thank you both!

I will give both a try and see what I get to work.

Thanks again!
Heather

 

[TLowder]

Did either of these post help? Please respond as to assist others that may have the same issue.

Tom

 

[hgraybill]

I'm on vacation until December 1st, so I won't be back in the office to test it until then. I will post my findings when I find out, though!

 

[hgraybill]

I was able to get the subreport to load using the first code example that was given when I loaded in the two separate connection strings.

Thanks for the help!