How do I change Database location at runtime (VB6 - RDC)?

*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:

Private Sub Form_Load()

'Variable declarations
Dim strServerOrDSNName As String
Dim strDBNameOrPath As String
Dim strUserID As String
Dim strPassword As String

strServerOrDSNName = "MyServer"
strDBNameOrPath = "NewDB"
strUserID = "user"
strPassword = "pwd"

'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

 

Syntax

Sub SetLogOnInfo (pServerName As String,    

[pDatabaseName], [pUserID], [pPassword])

Parameters

Parameter	Description
pServerName	Specifies the name of the server or ODBC data source where the database is located (that is, CRSS).
[pDatabaseName]	Specifies the name of the database.
[pUserID]	Specifies a valid user name for logging on to the data source.
[pPassword]	Specifies a valid password for logging on to the data source.

 

 

Source : http://www.tek-tips.com/faqs.cfm?fid=5374