Hi,
I just finished two reports in Crystal 10 using an Access Database (database  location and query are hardcoded in the reports.
I made a small vb 6 application to launch the reports. Now I need to change database location for the reports trough VB 6.  I have the following code, but it is not working. The reports do not show any data and I do not get any error. I think the problem is with the hardcoded query. The report is designed on this query and the field reference on the reports are like Command.Field1
I just want tho change database location in VB, but I want the report take the query that is in it.


I have the following code. How can I make them work.? Is there a better way?

Dim Report As New CR2 'set up the report
Dim conn3 As New ADODB.Connection  'ADO connection
Dim rs As New ADODB.Recordset      'rs connection

Function F_ViewReport()
'On Error GoTo End_FormLoad
    Dim sSQL2 As String
    gsRootJob = "4850"

"SELECT Material.Job, Material.Indicator, PO_Detail.Order, Source.Act_Qty,  Vendor.Name, PO_Header.PO, PO_Detail.Due_Date FROM (((Material LEFT JOIN Source ON Material.Material_Req = Source.Material_Req) LEFT JOIN PO_Detail ON Source.PO_Detail = PO_Detail.PO_Detail) LEFT JOIN PO_Header ON PO_Detail.PO = PO_Header.PO) LEFT JOIN Vendor ON PO_Header.Vendor = Vendor.Vendor WHERE (((Material.Job) Like '" & gsRootJob & "*')) ORDER BY Material.Job, PO_Header.PO, PO_Detail.Line;"

    Report.DiscardSavedData
    Set conn3 = New ADODB.Connection
    conn3.Open "provider=Microsoft.jet.OLEDB.4.0; Data Source=" & App.Path & "\Data tables.mdb"
    Set rs = New ADODB.Recordset 
    rs.Open sSQL2, conn3, adOpenKeyset, adLockReadOnly
    Report.Database.SetDataSource rs, , 1
   
    CRViewer2.ReportSource = Report
    CRViewer2.DisplayTabs = False
    CRViewer2.ViewReport
    CRViewer2.Zoom 1
   
   
    Screen.MousePointer = vbHourglass
  
Please help


Edited by copca - 30 Apr 2009 at 6:55pm

thanks.   :)
I tried this, and it is working. The query is like a table and with the report.database.tables(x).location = path & "\database.mdb" the report can retrieve the data from the query.

Dim crApp1 As New CRAXDrt.Application
Dim Report As New CRAXDrt.Report

Private Sub Form_Load()
On Error GoTo End_FormLoad
    Screen.MousePointer = vbHourglass
    Set Report = crApp1.OpenReport(App.Path & "\MyReport1.rpt")
    For x = 1 To Report.Database.Tables.Count
        Report.Database.Tables(x).Location = sDBPath & "\MyDataBase.mdb"
    Next x
    Report.DiscardSavedData
    Report.ParameterFields(1).ClearCurrentValueAndRange
    Report.ParameterFields(1).AddCurrentValue gsRootJob  'set parameter
    CRViewer1.ReportSource = Report
    CRViewer1.ViewReport
    Screen.MousePointer = vbDefault