Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Crystal and VB

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

by  vidru  Posted    (Edited  )
[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
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top