|
Subject:
|
Changing Database for Crystal Reports
|
|
Posted By:
|
pavel
|
Post Date:
|
11/17/2003 1:18:51 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
|
|
Reply By:
|
Jeff Mason
|
Reply Date:
|
11/17/2003 2:15:06 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:
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
|
|
Reply By:
|
pavel
|
Reply Date:
|
11/18/2003 10:12:46 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
|
|
Reply By:
|
HenryBStinson
|
Reply Date:
|
1/12/2004 12:03:19 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
|
|
Reply By:
|
bmeza
|
Reply Date:
|
12/5/2005 12:58:38 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\BraBasGlAuxSum.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
|
