×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Business Objects: Crystal Reports 3 Integrate FAQ

Crystal and VB

How do I change Database location at runtime (VB6 - RDC)? by vidru
Posted: 11 Aug 04 (Edited 4 Aug 05)

*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

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

*** 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.
 
-dave

Back to Business Objects: Crystal Reports 3 Integrate FAQ Index
Back to Business Objects: Crystal Reports 3 Integrate Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close