Setting database path at Run-Time for Crystal Report

[Shafan]

Hi,
I've designed a report based on a ADO database using a connection string. While building the connection string I pointed out the database from which the data is extracted.
The report runs on the development machine no problems, but when I try to run it on a deployment machine I get a Database Error:
"[Microsoft][ODBC Microsoft Access Driver] '(Unknown)' is not a valid path. Make sure the path name is spelled correctly and that you are connected to the server on which the file resides".

I pass an ADO recordset to the report so I'm no sure why the report tries to look for the database where it was at the design stage.

My question is: how can I set the database's path at run time (or how do I get rid of the error)?

Thanx

[dinesh123]

CrystalReport1.DataFiles(0) = App.Path & "\Server.mdb"

[Shafan]

I dont use the OCX so the above solution isn't applicable, but I tried the following code:
For Each crxDatabaseTable In
crxReport.Database.Tables
crxDatabaseTable.Location = database Path
Next crxDatabaseTable

but the error remains.
Could it be ODBC driver related issue?

[dinesh123]

I don't think this some thing to do with ODBC Driver

Check This

[Shafan]

no good mate - I'm using Crystal Reports 8.5

Thanx anyway

[Ayoob]

me also faced the same kind of problem, I am using sql server.
and passing oledb conection string to the report. in the development env its was working fine but once changed the database server its trowing an error "Cannot open sql"

Finaly I had to change the report database driver to ODBC.
(database-> convert database driver).

From ODBC can configure the server.
but still report expecting the connection string to fetch user name and the password.

hope this will help you.

[malleyo]

You can create your recordset within your program and pass Crystal Reports your ADO recordset. That way, you can control everything about the database at runtime with no problems.

I tried to attach a .pdf file from Crystal's website (support.crystaldecisions.com) that tells you how to set up your report and use VB to pass an ADO recordset, but the file is too big.

Here's the path to download the file:

http://support.crystaldecisions.com/...ttxado.pdf.asp

[kimmckenzie]

For those times when it's not possible to use Active data (aka ado recordsets as your report source) there are a few steps to go through to get the data source set correctly and get logged in.

 
 Public Sub PrintReport(sReportName As String, rsIds As ADODB.Recordset, sParamName As String, sParam2Name As String, sPrinterName As String, DataSource As String, Login As String, PWD As String, PaperSource As String) 
 
    Dim CRXApplication As New CRAXDRT.Application
    Dim CRXReport As CRAXDRT.Report
    Dim CRXDatabase As CRAXDRT.Database
    
    Set CRXReport = CRXApplication.OpenReport(sReportName, 1)
    Set CRXDatabase = CRXReport.Database

    SetTable CRXReport, DataSource, Login, PWD 
    CRXReport.PrintOut False, 1
   Set CRXReport = Nothing
    Set CRXDatabase = Nothing
    Set CRXApplication = Nothing
    
    If bError Then
        MsgBox "An error occurred opening reports  " & vbCrLf & _
                sErrorString, vbOKOnly + vbExclamation, "Report Open Error"
    End If
    
Exit Sub
err_PullTicket:
    sErrorString = sErrorString & Err.Description & vbCrLf
    
    App.LogEvent "PrintReport ERROR-" & Err.Number & "-" & Err.Description
    bError = True
    Resume Next

End Sub



Public Sub SetTable(Rpt As CRAXDRT.Report, DataSource As String, Login As String, PWD As String)


    Dim CRXTable As CRAXDRT.DatabaseTable
    Dim CRXTables As CRAXDRT.DatabaseTables
    Dim connects As Boolean
    Dim bError As Boolean
    Dim sErrorString As String
    
    bError = False
    
    On Error GoTo errSetTable

      Set CRXTables = Rpt.Database.Tables
      Set CRXTable = CRXTables.Item(1)
          CRXTable.SetLogOnInfo DataSource, "", Login, PWD
         connects = CRXTable.TestConnectivity
    Set CRXTable = Nothing
    Set CRXTables = Nothing
    
    If bError Then
        MsgBox "An error occurred setting report tables " & vbCrLf & 
                sErrorString, vbOKOnly + vbExclamation, "Set Table Error"
    End If
Exit Sub
errSetTable:
    sErrorString = sErrorString & Err.Description & vbCrLf
     App.LogEvent "ERROR-" & Err.Number & "-" & Err.Description
    bError = True
    Resume Next
    
End Sub

This is kind of copied and pasted together from a couple of different generic functions that I have so it probably won't work straight out (although it might) but you may also have to take into consideration any parameters and sub reports that you may have. The follwoing will work on any report with any sub report and it's short and sweet.

Public Function SetSubReport(objReport As CRAXDRT.Report, DataSource As String, Login As String, PWD As String) As Boolean
    
   Dim CRXSections As CRAXDRT.Sections

    Dim CRXSection As CRAXDRT.Section
    
    Dim CRXSubreportObj As CRAXDRT.SubreportObject
    
    Dim CRXReportObjects As CRAXDRT.ReportObjects
    
    Dim CRXSubreport As CRAXDRT.Report
    
    Dim CRXReportObject As Object
    
  
    
    Set CRXSections = objReport.Sections
    
      
    For Each CRXSection In CRXSections
    
        Set CRXReportObjects = CRXSection.ReportObjects
        For Each CRXReportObject In CRXReportObjects
            If CRXReportObject.Kind = crSubreportObject Then
                Set CRXSubreportObj = CRXReportObject
                Set CRXSubreport = CRXSubreportObj.OpenSubreport
                SetTable CRXSubreport, DataSource, Login, PWD
            End If
        Next CRXReportObject
    
    Next CRXSection
    
    
        
End Function