Print Page | Close Window

Dynamically change my database name

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=8072
Printed Date: 18 May 2024 at 11:04am


Topic: Dynamically change my database name
Posted By: Afi83
Subject: Dynamically change my database name
Date Posted: 20 Oct 2009 at 10:28pm
Hi there
I have an application in VB6 I want to use Crystal report (8.5) for reporting. In my application I have a ability to archive part of my database. Then I want to change my database location based on my database name. In my report I have subreport then I search about and find something to loop through all sections of report. The bellow code works fine just for my base database. But when I change database to archive one the report is empty.
The structure of two databases is exactly same then the problem is not there.
But I have to say basic report is based on the basic database then maybe this is the problem that I can't change the database through run time.
Please say me how can I prepare my report in crystal that can show from multiple database names.
Here is code:
Public CrxApp As CRAXDRT.Application
Public CrxRep As CRAXDRT.Report
Public crxSubreport As CRAXDRT.Report
Public crxSubreportObject As SubreportObject
Public crxDatabase As CRAXDRT.Database
Public crxDatabaseTables As CRAXDRT.DatabaseTables
Public crxDatabaseTable As CRAXDRT.DatabaseTable
Public crxSections As CRAXDRT.Sections
Public crxSection As CRAXDRT.Section
Public CRXReportObject As Object

Public Sub DataLoc(DataName As String, REP As String)
    Dim fi As String
    
    
    Set CrxApp = New CRAXDRT.Application
    fi = App.Path & "\" & REP & ".rpt"
    Set CrxRep = CrxApp.OpenReport(fi)

    Set crxSections = CrxRep.Sections
    
    For Each crxSection In crxSections
        For Each CRXReportObject In crxSection.ReportObjects
           If CRXReportObject.Kind = crSubreportObject Then
             Set crxSubreportObject = CRXReportObject
             Set crxSubreport = crxSubreportObject.OpenSubreport
             Set crxDatabase = crxSubreport.Database
             Set crxDatabaseTables = crxDatabase.Tables
             
             For Each crxDatabaseTable In crxDatabaseTables
    'If you are using a Native connection to PC database
    crxDatabaseTable.Location = App.Path & "\" & DataName & ".mdb"
    
             Next
             
             Set crxSubreport = Nothing
           End If
        Next
    Next
    
    
    'Set crxDatabase = Nothing
    'Set crxDatabaseTable = Nothing
    'Set crxDatabaseTables = Nothing
    'Set crxSections = Nothing
    'Set crxSection = Nothing
    'Set crxSubreportObject = Nothing
   ' Set crxReport = Nothing
   ' Set crxApplication = Nothing
End Sub
After this code I set my crystal viewer to Crxrep and it shows my report on base database.


Replies:
Posted By: lockwelle
Date Posted: 23 Oct 2009 at 6:39am
I am leery of any code that creates fields or changes items in a report.  Have you tried to changed the location/name/schema of a database in the report using the IDE?  It's not easy, and sometimes doesn't work the first time, so I would be doubtful that you can change the name / location of database. 
 
A simpler method, which I have seen done successfully, though don't care for, is to change the ODBC connection to point to the database that you want.
 
The best method, IMO, is to create the dataset in your application and then to push it the report.  The report doesn't know, and doesn't care where the data came from, just that the schema matches and the data is there.  The report is happy, the user is happy because the correct data is in the report, and this whole issue is moot.
 
HTH


Print Page | Close Window