[color green]*This has been tested with CR versions 8.5 and 9.0.[/color] [red](see addendum for CR XI toward the end of this FAQ)[/red]
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:
[color green]'General Declarations[/color]
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()
[color green]'Variable declarations[/color]
Dim strServerOrDSNName As String
Dim strDBNameOrPath As String
Dim strUserID As String
Dim strPassword As String
strServerOrDSNName = "MyServer"
strDBNameOrPath = "NewDB"
strUserID = "user"
strPassword = "pwd"
[color green]'Open the report[/color]
Set crxRpt = crxApp.OpenReport("C:\Employee_Profile.rpt")
[color green]'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.[/color]
crxRpt.Database.Tables(1).SetLogOnInfo strServerOrDSNName, _
strDBNameOrPath, strUserID, strPassword
[color green]'This removes the schema from the Database Table's Location property.[/color]
Set crxTables = crxRpt.Database.Tables
For Each crxTable In crxTables
With crxTable
.Location = .Name
End With
Next
[color green]'Loop through the Report's Sections to find any subreports, _
and change them as well[/color]
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)
[color green]'Open the subreport, and treat like any other report[/color]
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
[color green]'View the report[/color]
Viewer.ReportSource = crxRpt
Viewer.ViewReport
End Sub
[red]*** Addendum - Aug 2005 ***[/red]
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:
[color green]'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.[/color]
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.
-dave
