*This has been tested with CR versions 8.5 and 9.0.(see addendum for CR XI toward the end of this FAQ)
Problem: A report was designed against a development database. When the report is deployed, the information on the report is still coming from the development database instead of the production database.
Crystal saves connection information within each report file. When running the report from VB, it will try to use that information to connect to the same Server/Database/Table.
"Hands On" Solution: Open the Report in the Crystal designer, go to the Database menu > Set Location (or Set Datasource Location in CR 9), and browse to the correct location. This can be tedious if you have several reports to deal with.
Runtime Solution: This method hasn't failed me yet using either ODBC DSN's or Native/OLEDB connections to SQL Server and Access.
1) In VB, start a new project, and add the "Crystal Report Viewer Control" component. Add a Reference to the "Crystal Reports [version] ActiveX Designer Run Time Library".
2) Place a CRViewer on the form, and name it "Viewer".
3) In the General Declarations section of Form1, add the following declarations:
CODE
'General Declarations Dim crxApp As New CRAXDRT.Application Dim crxRpt As CRAXDRT.Report Dim crxTables As CRAXDRT.DatabaseTables Dim crxTable As CRAXDRT.DatabaseTable Dim crxSubreportObject As CRAXDRT.SubreportObject Dim crxSubReport As CRAXDRT.Report Dim crxSections As CRAXDRT.Sections Dim crxSection As CRAXDRT.Section
4) Place the following in the Form_Load event of Form1:
CODE
Private Sub Form_Load()
'Variable declarations Dim strServerOrDSNName As String Dim strDBNameOrPath As String Dim strUserID As String Dim strPassword As String
'Open the report Set crxRpt = crxApp.OpenReport("C:\Employee_Profile.rpt")
'Set the connection for the report. 'SetLogOnInfo is a deprecated method in CR 9, _ but still works. 'The suggested method for CR9 is to use _ the ConnectionProperty object. crxRpt.Database.Tables(1).SetLogOnInfo strServerOrDSNName, _ strDBNameOrPath, strUserID, strPassword
'This removes the schema from the Database Table's Location property. Set crxTables = crxRpt.Database.Tables For Each crxTable In crxTables With crxTable .Location = .Name End With Next
'Loop through the Report's Sections to find any subreports, _ and change them as well Set crxSections = crxRpt.Sections
For i = 1 To crxSections.Count Set crxSection = crxSections(i)
For j = 1 To crxSection.ReportObjects.Count
If crxSection.ReportObjects(j).Kind = crSubreportObject Then Set crxSubreportObject = crxSection.ReportObjects(j)
'Open the subreport, and treat like any other report Set crxSubReport = crxSubreportObject.OpenSubreport Set crxTables = crxSubReport.Database.Tables
For Each crxTable In crxTables With crxTable .SetLogOnInfo strServerOrDSNName, _ strDBNameOrPath, strUserID, strPassword .Location = .Name End With Next
End If
Next j
Next i
'View the report Viewer.ReportSource = crxRpt Viewer.ViewReport
End Sub
*** Addendum - Aug 2005 *** The above described method works fine, unless a table is aliased, (the .Location = .Name line). I'm still working on a definitive workaround for CR 8.5, but the solution in CR XI is relatively easy, but at first glance might look odd:
CODE
'This removes the schema from the Database Table's Location property. 'In CR XI (and maybe in CR 9 and up), when reading the Location property, ' only the table/procedure name is returned, without the schema. 'By setting the Location equal to itself, we are potentially overwriting ' any schema saved with the report, which is a good thing. Set crxTables = crxRpt.Database.Tables For Each crxTable In crxTables With crxTable .Location = .Location End With Next
As I test with other versions, I'll update the FAQ as needed. Feel free to send me any comments, suggestions, issues, etc.
Talk To Other Members