Print Page | Close Window

Change Database location trough VB

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=6229
Printed Date: 01 Apr 2025 at 6:48am


Topic: Change Database location trough VB
Posted By: copca
Subject: Change Database location trough VB
Date Posted: 30 Apr 2009 at 6:54pm
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


Replies:
Posted By: lockwelle
Date Posted: 01 May 2009 at 6:31am
I don't know all of the details, but I get you started on the path.  Change the report to OLE connection (use the database location and do lots of updates, the connection and each table or store proc).  This doesn't bind to the ODBC driver, so the database is allowed to float. 
 
I realize that it is not VB6 notation, but I sure that you can translate:

Dim crtableLogoninfo As New TableLogOnInfo

Dim crConnectionInfo As New ConnectionInfo

Dim CrTables As Tables

Dim CrTable As Table

With crConnectionInfo

.ServerName = SQLServer

.DatabaseName = Database

.UserID = DatabaseUser

.Password = DatabasePassword

End With

CrTables = report.Database.Tables

Try

For Each CrTable In CrTables

crtableLogoninfo = CrTable.LogOnInfo

crtableLogoninfo.ConnectionInfo = crConnectionInfo

CrTable.ApplyLogOnInfo(crtableLogoninfo)

If CrTable.TestConnectivity() Then

If CrTable.Location.IndexOf(".") > 0 Then

CrTable.Location = CrTable.Location.Substring(CrTable.Location.LastIndexOf(".") + 1)

Else

CrTable.Location = CrTable.Location

End If

Else

MsgBox("Please Check Connection Settings", MsgBoxStyle.Critical, "Connection Error")

End If

Next

Catch ex As Exception

MsgBox("Please Check Connection Settings", MsgBoxStyle.Critical, "Connection Error")

End Try
 
HTH

Posted By: copca
Date Posted: 01 May 2009 at 10:56am
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


Print Page | Close Window