CR9 problems

I am using Crystal Report 9 and vb6 with Sql server 2000 as backend.

I have a report work perfectly on one server. Now I need to change it to work on different servers.

The fields are selected from 4 tables on design mode. In vb code, it set criteria to select the records to display.
Here is the original code to run on current server.

Code:

---


    strReport = "\Overrides.rpt"
   
     
    strSelection = "{TRANS.TRANDATE} = DateTime (" & Format(dtpDate.Value, "yyyy") & "," & Format(dtpDate.Value, "mm") & "," & Format(dtpDate.Value, "dd") & ", 00, 00, 00) and "
    strSelection = strSelection & "{TRANS.ROUTE_NUM} = '" & gsCheckinRouteNum & "' and "
    strSelection = strSelection & " {TRANS.PACKET_NUM} <> '' "
   
    If cmbBranch.Text <> "ALL" Then
        strSelection = strSelection & "and  {Person.Per_Branch} = '" & cmbBranch.Text & "' "
    End If
   
    Set crReport = crApp.OpenReport(gsTrack & "\Reports\" & strReport)
    crReport.RecordSelectionFormula = strSelection

           
    If optPrint(0).Value = True Then
        ' Print to screen
        frmCR9Report.Visible = False
        frmCR9Report.CRV9.ReportSource = crReport
       
        frmCR9Report.CRV9.ViewReport
        frmCR9Report.CRV9.EnableGroupTree = False
        frmCR9Report.Show vbModal
        Set frmCR9Report = Nothing
    Else
        crReport.PrintOut False, 1
    End If

---

Now I need change it able to connect to different server,

I added codes (in red), but didn't get the same result.

Code:

---

    strReport = "\Overrides.rpt"
   
   
    ' Set the connection to the SQL database that we will pass to the report
    strConnection = "Driver={SQL Server};" & _
          "Server=" & gsTrackingSQL & ";" & _
          "Database=" & gsTrackDatabaseName & ";" & _
          "Uid=" & gstrTrkUsr & ";" & _
          "Pwd=" & gstrTrkPwd   

    strSelection = "{TRANS.TRANDATE} = DateTime (" & Format(dtpDate.Value, "yyyy") & "," & Format(dtpDate.Value, "mm") & "," & Format(dtpDate.Value, "dd") & ", 00, 00, 00) and "
    strSelection = strSelection & "{TRANS.ROUTE_NUM} = '" & gsCheckinRouteNum & "' and "
    strSelection = strSelection & " {TRANS.PACKET_NUM} <> '' "
   
    If cmbBranch.Text <> "ALL" Then
        strSelection = strSelection & "and  {Person.Per_Branch} = '" & cmbBranch.Text & "' "
    End If
   
    Set crReport = crApp.OpenReport(gsTrack & "\Reports\" & strReport)
    Set adoRS = New ADODB.Recordset
    adoRS.Open "Trans", strConnection, adOpenDynamic, adLockBatchOptimistic   
    crReport.Database.Tables(1).SetDataSource adoRS, 3   
    crReport.RecordSelectionFormula = strSelection

           
    If optPrint(0).Value = True Then
        ' Print to screen
        frmCR9Report.Visible = False
        frmCR9Report.CRV9.ReportSource = crReport
       
        frmCR9Report.CRV9.ViewReport
        frmCR9Report.CRV9.EnableGroupTree = False
        frmCR9Report.Show vbModal
        Set frmCR9Report = Nothing
    Else
        crReport.PrintOut False, 1
    End If

---

Could any one point out what's wrong in my code? Thanks a lot.

Crystal Reports' report designed using OLEDB (ADO) as data source

This example demonstrates how to connect to an OLEDB (ADO) data source,
change the data source, and change the database by using the ConnectionProperty Object, as well as how to change the table name by using the Location property of the DatabaseTable Object.
CrystalReport1 is created using an ODBC data source connected to the pubs database on Microsoft SQL Server.
The report is based off the authors table.

Code:

---

' Create a new instance of the report.
Dim Report As New CrystalReport1

Private Sub Form_Load()

  ' Declare a ConnectionProperty object.
  Dim CPProperty As CRAXDRT.ConnectionProperty
 
  ' Declare a DatabaseTable object.
  Dim DBTable As CRAXDRT.DatabaseTable

  ' Get the first table in the report.
  Set DBTable = Report.Database.Tables(1)

  ' Get the "Data Source" property from the
  ' ConnectionProperties collection.
  Set CPProperty = DBTable.ConnectionProperties("Data Source")

  ' Set the new data source (server name).
  ' Note: You do not need to set this property if
  ' you are using the same data source.
  CPProperty.Value = "Server2"

  ' Get the "User ID" property from the
  ' ConnectionProperties collection.
  Set CPProperty = DBTable.ConnectionProperties("User ID")

  ' Set the user name.
  ' Note: You do not need to set this property if
  ' you are using the same user name.
  CPProperty.Value = "UserName"

  ' Get the "Password" property from the
  ' ConnectionProperties collection.
  Set CPProperty = DBTable.ConnectionProperties("Password")

  ' Set the password.
  ' Note: You must always set the password if one is required.
  CPProperty.Value = "Password"

  ' Get the "Initial Catalog" (database name) property from the
  ' ConnectionProperties collection.
  ' Note: You do not need to set this property if
  ' you are using the same database.
  Set CPProperty = DBTable.ConnectionProperties("Initial Catalog")

  ' Set the new database name.
  CPProperty.Value = "master"

  ' Set the new table name.
  DBTable.Location = "authors2"

  Screen.MousePointer = vbHourglass
  ' Set the report source of the viewer and view the report.
  CRViewer91.ReportSource = Report
  CRViewer91.ViewReport
  Screen.MousePointer = vbDefault

End Sub

---

Hope this help