crystal reports 8 - set location dynamically in vb6

I have a VB6 front end, SQL Server 2005 as the back end and Crystal Reports 8.5 for reports.

I need to set the location at run time in my application as I have 2 databases. My problem is that when I change database, b开发者_开发百科ut the location remain the same. It will be great if anyone can help me out. Thanks in advance for your time, and here is my code.

Private Sub prin_Click()
With CrystalReport1
    .Connect = MDI1.txtcn --> this is my connection info "driver={sql server};server=server;database=database;uid=user;pwd=password"        
    .DiscardSavedData = True
    .Action = 1
    .PrintReport
End With

Try some code like this:

Private Sub cmdSetLocations_Click()
    Dim CrxApp As New CRAXDRT.Application
    Dim CrxRep As CRAXDRT.Report
    Dim CrxSubRep As CRAXDRT.Report

    Dim strReport As String
    Dim i As Integer, ii As Integer

    strReport = "[Path to report file]"
    Set CrxRep = CrxApp.OpenReport(strReport)

    SetReportLocation CrxRep

    For i = 1 To CrxRep.Sections.Count
        For ii = 1 To CrxRep.Sections(i).ReportObjects.Count
            If CrxRep.Sections(i).ReportObjects(ii).Kind = crSubreportObject Then
                Set CrxSubRep = CrxRep.OpenSubreport(CrxRep.Sections(i).ReportObjects(ii).SubreportName)
                SetReportLocation CrxSubRep
            End If
        Next ii
    Next

    'open your report in the report viewer

    Set CrxApp = Nothing
    Set CrxRep = Nothing
    Set CrxSubRep = Nothing
End Sub

Private Sub SetReportLocation(ByRef RepObj As CRAXDRT.Report)
    Dim CrxDDF As CRAXDRT.DatabaseTable
    Dim CP As CRAXDRT.ConnectionProperties

    For Each CrxDDF In RepObj.Database.Tables
        Set CP = CrxDDF.ConnectionProperties
        CP.DeleteAll
        CP.Add "Connection String", "[Your connection string goes here]"
    Next

    Set CrxDDF = Nothing
    Set CP = Nothing

End Sub

With CR
     .ReportFileName = App.Path + "\Labsen2.rpt"
     .SelectionFormula = "{PersonalCalendar.PersonalCalendarDate}>= Date(" & Year(DTPicker1) & "," & Month(DTPicker1) & "," & Day(DTPicker1) & ") and {PersonalCalendar.PersonalCalendarDate}<=date(" & Year(DTPicker2) & "," & Month(DTPicker2) & "," & Day(DTPicker2) & ") and {Department.DepartmentName}= '" & Combo1.Text & "'"
     .Formulas(0) = "tglAwal = '" & DTPicker1.Value & "'"
     .Formulas(1) = "tglAkhir = '" & DTPicker2.Value & "'"
     .Password = Chr(10) & "ithITtECH"
     .RetrieveDataFiles
     .WindowState = crptMaximized
     .Action = 1
 End With

Try formatting the connection string like this:

DSN=server;UID=database;PWD=password;DSQ=user

The meanings of DSN, UID, DSQ are counter-intuitive, they are overloaded by Crystal.

Also check you have no subreports whose Connect properties would need to be similarly changed.

Why not pass the recordset to your report? In this way you will be able to get data from any supported (i mean VB6 can connect to) databases dynamically, you can even merge data from multiple databases, your report will require the data(recordset) only and report will be created with Data Field Definition.