Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Pro VB 6
|
Pro VB 6 For advanced Visual Basic coders working in version 6 (not .NET). Beginning-level questions will be redirected to other forums, including Beginning VB 6.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB 6 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 17th, 2003, 02:18 PM
Authorized User
 
Join Date: Jul 2003
Posts: 58
Thanks: 0
Thanked 0 Times in 0 Posts
Default Changing Database for Crystal Reports

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

 
Old November 17th, 2003, 03:15 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old November 18th, 2003, 11:12 AM
Authorized User
 
Join Date: Jul 2003
Posts: 58
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old January 12th, 2004, 01:03 PM
Registered User
 
Join Date: Jan 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old December 5th, 2005, 01:58 PM
Registered User
 
Join Date: Dec 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
Crystal Reports - DataBase Login - C# aranhamarvel C# 0 April 11th, 2007 02:16 PM
Changing Database location for crystal report swarnapy VB How-To 0 August 27th, 2006 11:37 PM
changing sql query of crystal reports from program connect2sandep Crystal Reports 1 August 22nd, 2006 01:15 PM
Crystal Reports and Access Database solemgar Crystal Reports 0 August 18th, 2005 02:35 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.