Changing Database for Crystal Reports

pavel · November 17th, 2003, 02:18 PM

Great Day to everyone.

We are creating the application with 70+ reports.
All reports are created with connection to our test Database. Field names are the same as in real one. But how to change database name for every single report. We've tried to change database name from VB6 program but it shows the data from our test database. We just realized it two days before our dead line, so please help.

Pavel

Jeff Mason · November 17th, 2003, 03:15 PM

You set the database by opening the report object in code and looping through the various table objects contained in the report and executing the 'SetLoginInfo' method on the data table object. Use code like this fragment:

Code:

   Set crxApp = New CRAXDRT.Application
   Set crxReport = crxApp.OpenReport(<path to rpt file>, 1)

   ' Set the main report's database location
   For Each crxDataTable In crxReport.Database.Tables
      If UseNTAuthentication Then
         crxDataTable.SetLogOnInfo <ServerName>, <Database>, "<<Use Integrated Security>>", vbNullString
      Else
         crxDataTable.SetLogOnInfo <ServerName>, <Database>,  <DBUserName>, <DBPassword>
      End If
      I = InStr(crxDataTable.Location, ".")
      If I = 0 Then
         crxDataTable.Location = <Database> & ".dbo." & crxDataTable.Location
      Else
         crxDataTable.Location = <Database> & Mid$(crxDataTable.Location, I)
      End If
      If Not crxDataTable.TestConnectivity Then
         sMsg = "Unable to connect to database."
         Set crxReport = Nothing
         Set crxApp = Nothing
         Exit Function
      End If
   Next

   'Subreports
   For Each crxSection In crxReport.Sections
      'cycle through the objects in each section
      For Each ReportObject In crxSection.ReportObjects
         'test the objects to see if they're subreports
         If ReportObject.Kind = crSubreportObject Then
            Set crxSubReportObj = ReportObject
            'Finally, open the subreport and treat it as you would any other Report
            Set crxSubReport = crxSubReportObj.OpenSubreport
            For Each crxDataTable In crxSubReport.Database.Tables
               If UseNTAuthentication Then
                  crxDataTable.SetLogOnInfo <Server>, <Database>, "<<Use Integrated Security>>", vbNullString
               Else
                  crxDataTable.SetLogOnInfo <Server>, <Database>, <DBUserName>, <DBPassword>
               End If
               I = InStr(crxDataTable.Location, ".")
               If I = 0 Then
                  crxDataTable.Location = <Database> & ".dbo." & crxDataTable.Location
               Else
                  crxDataTable.Location = <Database> & Mid$(crxDataTable.Location, I)
               End If
               If Not crxDataTable.TestConnectivity Then
                  sMsg = "Unable to connect subreport '" & crxSubReportObj.SubreportName & "' table '" & crxDataTable.Name & "' to database."
                  Set crxReport = Nothing
                  Set crxApp = Nothing
                  Exit Function
               End If
            Next     ' crxDataTable
         End If
      Next           ' ReportObject
   Next              ' crxSection

Note that you must substitute appropriate values for <server>, <database>, etc. and define Crystal objects appropriately.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

pavel · November 18th, 2003, 11:12 AM

Hi Jeff.
Thank you for the answer.
We are using the same code in our application. The only part we were missing is:

I = InStr(crxDataTable.Location, ".")
If I = 0 Then
                  crxDataTable.Location = <Database> & ".dbo." & crxDataTable.Location
               Else
                  crxDataTable.Location = <Database> & Mid$(crxDataTable.Location, I)
End If

As soon as we added this code, we had an error on line

I = InStr(crxDataTable.Location, ".")

Do you have any ideas why?

Thank you again.
Pavel

HenryBStinson · January 12th, 2004, 01:03 PM

I found out how to convert a CR report to ttx files without losing the field objects. A friend of mine sent me this. Open top menu item "Database". Then click on "Set Database Location". From the window form that pops up, you can select another data source and click "Replace". This worked fine, except that my one Group-by section in the form kind of lost its mind -- was still pointing to the Command.<FieldName>, which no longer existed. I'll figure out how to handle that and get back to you.

Thanks to my friend, Subbu, from India.

Henry Stinson, BSECE
Senior Software Engineer specializing in VB

bmeza · December 5th, 2005, 01:58 PM

hi i try the solutions but i still see in my report the location set by default at cr do you now why ? im using cr8.5 and vb6.0 here its my code
Set Reporte = crApp.OpenReport("\\brasant04\input\reports\BraBas GlAuxSum.rpt")
    For Each crxDBTable In Reporte.Database.Tables
         crxDBTable.SetLogOnInfo GsServidor1, GsBaseDatos1, GsUsuario1, GsPassword1

      I = InStr(crxDBTable.Location, ".")
      If I = 0 Then
         crxDBTable.Location = GsBaseDatos1 & ".dbo." & crxDBTable.Location
      Else
         crxDBTable.Location = GsBaseDatos1 & Mid$(crxDBTable.Location, I)
      End If
      If Not crxDBTable.TestConnectivity Then
         sMsg = "Unable to connect to database."
         Set crxreporte = Nothing
         Set crxApp = Nothing
         End
      End If
   Next