Set dB location in code for CRXI

[SparrowHawk7]

I've got CRXI that I'm using in a VB6 app - linking to a database. I just ran into a problem when I moved the application directory and the reports lost their links to the database and complained that they weren't logged in. I reset the connection but got to thinking this could cause a problem for my end users should they move anything. Is there a way to set the database location in code when I call the report?

Also, my app changes data quite a bit requiring a refresh of the report pretty often. Is there a way to do this with code so things are up to date on the report when it's opened? To be more clear ... if I open the report, close it and then open it again after some information has changed, the original data is still visible. If I press the reset button it is updated. I would like to programmatically refresh the data rather than force the user to do it.

TIA
Ken

[jggtz]

Post the code where You call the reports

[SparrowHawk7]

It's just the boilerplate that CRXI sets up ...

Dim Report As New GameStats
Option Explicit

Private Sub Form_Load()
Screen.MousePointer = vbHourglass
CRViewer1.ReportSource = Report
CRViewer1.ViewReport
Screen.MousePointer = vbDefault

End Sub

Private Sub Form_Resize()
CRViewer1.Top = 0
CRViewer1.Left = 0
CRViewer1.Height = ScaleHeight
CRViewer1.Width = ScaleWidth

End Sub

I tried adding Report.DiscardSavedData in the form activate event but that did nothing.

[jggtz]

The next is an example to show a Crystal Reports' report from a VB6 app
In this example, the report is designed using ADO OLE DB (MS Access)
as data source, then you have to modify to your data source requirements;
(in order to do so , enter to Crystal Reports, select: menu /
Database / SetDatasourceLocation / Current Data Source / Properties
to display the properties you have to provide thru vb code )

Project References : Crystal Report Active X Designer Run Time 11
Project Components : Crystal Active X Report Viewer Library 11

'Declarations

Public CRApplication    As New CRAXDRT.Application
Public CRReport         As New CRAXDRT.Report
Public CRConProp        As CRAXDRT.ConnectionProperty
Public CRTabla          As CRAXDRT.DatabaseTable
Public GRpt             As String  'Contains the report's name
Public MyFolder		As String  'Contains the relative path to data 
and reports
Public MInt2		As Integer 'Just a counter 
Public GSelFor		As String  'Contains the RecordSelectionFormula
Public GTitleA		As String  'Contains the Company's name
Public GTitleB		As String  'Contains the Report's name
Public GTitleC		As String  'Contains the dates's range
'
'
'
Sub PrintReport()
    'here set the report to show
    Set CRReport = CRAplicacion.OpenReport(MyFolder & "\" & GRpt, 1)

    'here change to the new data path to all the tables defined in the 
rpt
    'in this example all the tables are inside the same mdb file
    'here the datasource is thru ADO OLEDB (MS Access), 
    'if it is other datasource, as SQL, the ConnectionProperties will 
be different
    'Do not use if the relative data path doesn't change
    For MInt2 = 1 To CRReport.Database.Tables.Count
        Set CRTabla = CRReporte.Database.Tables(MInt2)
        Set CRConProp = CRTabla.ConnectionProperties("Database Type")
            CRConProp.Value = "OLE DB (ADO)"
        Set CRConProp = CRTabla.ConnectionProperties("Provider")
            CRConProp.Value = "Microsoft.Jet.OLEDB.4.0"
        Set CRConProp = CRTabla.ConnectionProperties("Data Source")
            CRConProp.Value = MyFolder & "\MyDataFile.mdb"
        Set CRConProp = CRTabla.ConnectionProperties("User Id")
            CRConProp.Value = "Admin"
        Set CRConProp = CRTabla.ConnectionProperties("Database Type")
            CRConProp.Value = "Access"
        Set CRConProp = CRTabla.ConnectionProperties("Locale 
Identifier")
            CRConProp.Value = "1033"
        Set CRConProp = CRTabla.ConnectionProperties("OLE DB 
Services")
            CRConProp.Value = "-6"
    Next MInt2
    
    'Modify existing rpt Formulas 
    'Do not use if there are no formulaes
    For MInt2 = 1 To CRReport.FormulaFields.Count
        Select Case CRReport.FormulaFields(MInt2).Name
            Case "{@a}"
                CRReport.FormulaFields(MInt2).Text = "Trim(" & Chr(39) 
& GTitleA & Chr(39) & ")"
            Case "{@b}"
                CRReport.FormulaFields(MInt2).Text = "Trim(" & Chr(39) 
& GTitleB & Chr(39) & ")"
            Case "{@c}"
                CRReport.FormulaFields(MInt2).Text = "Trim(" & Chr(39) 
& GTitleC & Chr(39) & ")"
        End Select
    Next MInt2
    
    'Modify record selection criteria
    'Do not use if there is not record selection or this is not going 
to change
    If Trim(GSelFor) <> "" Then
        CRReport.RecordSelectionFormula = GSelFor
    End If
    
    'frmReport is the form that contains the CR11Viewer
    frmReport.Show vbModal   
    
    'Release resources
    Set CRTabla = Nothing
    Set CRConProp = Nothing
    Set CRReport = Nothing
    Set CRAplicacion = Nothing

This is a way, but there are some other ways that do the same thing

JG

[SparrowHawk7]

Thanks for your help JG. I may not have explained myself very well.

I created the report in CRXI Developer's Edition - not the designer. Then I put the report in my application directory and added it to my VB project. It draws it's data from a mdb file also located in the program directory. That's why the code I posted here was generic boilerplate that CR put in the form automatically.

Where this all began was that I moved the IDE application directory which caused the report to lose contact with the database. This leads me to think that trouble might arise when the application is distributed and the end user puts the program in their own directory. So then, how can I tell the report to look in the application directory for the mdb file it needs (overriding what the report remembers of how it was created) .. and the table within that dB that it draws it's data from?

The other extremely annoying thing is that the data seems to persist within the report. In other words, if I open the report at one point in using the program and close it, then work more within the program changing data and then reopen the report, the old data shows. If I wait a second or so and push the refresh button on the report, the data THEN is updated. Is there a way to force the report to update it's data when it's opened? (I am not saving the data with the report, incidentally).

Thank you very much for helping out!
Ken

[jggtz]

The code I posted is ok, here goes a tested piece of code using the generic boilerplate that CR put in the form automatically
I only intermix some of my code

Option Explicit

Dim Report          As New CrystalReport1

Public CRTabla      As CRAXDRT.DatabaseTable         
Public MInt2        As Integer 'Just a counter            
Public CRConProp    As CRAXDRT.ConnectionProperty

Private Sub Form_Load()

    Screen.MousePointer = vbHourglass
    For MInt2 = 1 To Report.Database.Tables.Count
        Set CRTabla = Report.Database.Tables(MInt2)
        Set CRConProp = CRTabla.ConnectionProperties("Database Type")
            CRConProp.Value = "OLE DB (ADO)"
        Set CRConProp = CRTabla.ConnectionProperties("Provider")
            CRConProp.Value = "Microsoft.Jet.OLEDB.4.0"
        'Here We are changing to the new data source path
        Set CRConProp = CRTabla.ConnectionProperties("Data Source")
            CRConProp.Value = "C:\NewFolder\NewDBName.mdb"
        Set CRConProp = CRTabla.ConnectionProperties("User Id")
            CRConProp.Value = "Admin"
        Set CRConProp = CRTabla.ConnectionProperties("Database Type")
            CRConProp.Value = "Access"
        Set CRConProp = CRTabla.ConnectionProperties("Locale Identifier")
            CRConProp.Value = "1033"
        Set CRConProp = CRTabla.ConnectionProperties("OLE DB Services")
            CRConProp.Value = "-6"
    Next MInt2    
    CRViewer1.ReportSource = Report
    CRViewer1.ViewReport
    Screen.MousePointer = vbDefault

End Sub

Private Sub Form_Resize()

    CRViewer1.Top = 0
    CRViewer1.Left = 0
    CRViewer1.Height = ScaleHeight
    CRViewer1.Width = ScaleWidth
    
End Sub

[SparrowHawk7]

Thanks anyway, JG. I pasted the code in my report and started getting subscript out of range errors concerning the provider line. The report works fine without the code. I wonder why my version did not create code like that when I added the report.

[jggtz]

May You attach the rpt saved with data?