Not working. Change database location trough VB 6

34 Views

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 to do it?

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"

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!

cc