Anders Hesselbom

For this example, I have downloaded the Access 2000 Northwind database from http://www.microsoft.com/. If you google on the filename (nwind.mdb), make sure that you download it from Microsoft’s web site.

The problem I have with Crystal Reports is that when I distribute my application, the data source in my report gets invalid, since the connection data refers to a file on my computer, the filename gets invalid when the application is installed on the end users machine.

To solve this problem, the connection data must be dynamically assigned when an end user opens a report.

An application with a Crystal Report usually has a report (rpt-file) added to the project, and a Crystal Report Viewer (a Windows Form with a CrystalReportViewer control). The report file contains the connection string that points to the mdb-file, and the Crystal Report Viewer has a reference to the rpt-file.

All I have to do to simulate the problem, is to rename the mdb-file from nwind.mdb to nwind1.mdb (I close Visual Studio before I do this, to release any locks). Now, Crystal Reports will prompt me for a login ID and a password, since he can’t connect to the file. There is no reason to give a login ID, since the problem is that the file isn’t found (stupid reaction to that error). No matter what I do, the report will not load.

I couldn’t find any information on this, so it took me a while to track how the rpt-file works. This is the solution:

In the form that contains the report viewer, type some code in the control’s Load event, here:

Private Sub CrystalReportViewer1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles CrystalReportViewer1.Load

Declare a variable that can point at the report document:

Dim Doc As CrystalDecisions.CrystalReports.Engine.ReportDocument

Assign the current document to the variable:

Doc = CType(Me.CrystalReportViewer1.ReportSource, _
CrystalDecisions.CrystalReports.Engine.ReportDocument)

Finally, call the SetConnection in the connection object you want to modify. Use the filename as the server and database argument.

Doc.DataSourceConnections(0).SetConnection( _
“C:\Temp\Nwind1.mdb”, “C:\Temp\Nwind1.mdb”, False)

Now, you have a code that allows you to dynamically modify the location of the underlying database of crystal report!