Change Crystal report DB location at run time for VB6 3

[WdnUlik2no]

I have a VB6 application that is able to view a Crystal report using the database that is defined in the .rpt file. This works fine and is able to generate updated reports as the DB changes, but we have many different databases (test, prod, dev, etc), and I have been trying since last night to get the VB app to change the reports database at runtime so I don't have to go in the report and manauly change that database location everytime or create a seperate report for each enviroment. I tried the solution on this site (faq-768-5374), but the app is still using the database defined in the .rpt file.

Here is the code:

Dim crxApp As New CRAXDRT.Application
Dim crxRpt As CRAXDRT.Report
Dim crxTables As CRAXDRT.DatabaseTables
Dim crxTable As CRAXDRT.DatabaseTable
Dim crxSubreportObject As CRAXDRT.SubreportObject
Dim crxSubReport As CRAXDRT.Report
Dim crxSections As CRAXDRT.Sections
Dim crxSection As CRAXDRT.Section

Private Sub Command1_Click()
Viewer.Refresh
End Sub

Private Sub Form_Load()
'Variable declarations
Dim strServerOrDSNName As String
Dim strDBNameOrPath As String
Dim strUserID As String
Dim strPassword As String

strServerOrDSNName = "dev.ourapp.com/appdev"

strDBNameOrPath = "com.mysql.jdbc.Driver;"
strUserID = "appuser"
strPassword = "apppassword"

Set crxRpt = crxApp.OpenReport("C:\test.rpt")


crxRpt.Database.Tables(1).SetLogOnInfo strServerOrDSNName, _
strDBNameOrPath, strUserID, strPassword

Set crxTables = crxRpt.Database.Tables
For Each crxTable In crxTables
With crxTable
.Location = .Name
End With
Next

'Loop through the Report's Sections to find any subreports, _
and change them as well
Set crxSections = crxRpt.Sections

For i = 1 To crxSections.Count
Set crxSection = crxSections(i)

For j = 1 To crxSection.ReportObjects.Count

If crxSection.ReportObjects(j).Kind = crSubreportObject Then
Set crxSubreportObject = crxSection.ReportObjects(j)

'Open the subreport, and treat like any other report
Set crxSubReport = crxSubreportObject.OpenSubreport
'*****************************************
Set crxTables = crxSubReport.Database.Tables

For Each crxTable In crxTables
With crxTable
.SetLogOnInfo strServerOrDSNName, _
strDBNameOrPath, strUserID, strPassword
.Location = .Name
End With
Next


'****************************************
End If

Next j

Next i

'View the report
Viewer.ReportSource = crxRpt
Viewer.ViewReport



End Sub


No matter what I set strServerOrDSNName and strDBNameOrPath to, the report still connects to our test DB as defined in the .rpt file. In fact if I leave these two fields blank it still connects the the test DB with no problem. What am I doing wrong? Thanks.

 

[WdnUlik2no]

BTW I am using Crystal Reports XI

 

[jjames]

I'm still using Crystal 9.0 but this is how I change the database....

Dim crxReport as CRAXDRT.Report
Dim crxProperty as CRAXDRT.ConnectionProperty
Dim crxDBTable as CRAXDRT.DatabaseTable

Set crxReport = crxApp.OpenReport("Test.rpt")
Set crxDBTable = crxReport.Database.Tables(1)
Set crxProperty = DBTable.ConnectionProperties("Database Name")
crxProperty.Value = strDBNameOrPath

.
.
.

 

[WdnUlik2no]

Hi thanks for you response!

I have tried what you suggested, but see that my connection buffer string does not have a "Database Name" item in the connection properties. So when I tried the statement:

SetcrxProperty = crxDBTable.ConnectionProperties("Database Name")

I get a "Subscript out of range error"

I printed out all of my connection properties and this is all I have. The only databasename is under the PreQEDatabaseName, (I have no idea what that is BTW). the PreQ stuff only appears in the print out if I populate the
strServerOrDSNName and strDBNameOrPath variables: Like below:

strServerOrDSNName = "dev.ourapp.com/appdev"
strDBNameOrPath = "com.mysql.jdbc.Driver;"

If I leave these values blank then there is nothing after the Generic JDBC Driver entry below:


Connection Properties (printed out using this line of code)
Form2.Text1.Text = Form2.Text1.Text & strItem & ": [" & crxRpt.Database.Tables(1).ConnectionProperties.Item(strItem) & "]" + vbCrLf
==============================================================================

Use JDBC: [True]

Connection URL: [jdbc:mysql://test.ourapp.com/ourapptest?user={userid}&password={password}]

Database Class Name: [com.mysql.jdbc.Driver]

JNDI Datasource Name: []

Server: [test.ourapp.com/ourapptest]

User ID: [appuser]

****Can't Display Password**** (this is a default string I used because it wouldn't let me print out the pasword field so I just skipped it)

JDBC Connection String: [!com.mysql.jdbc.Driver!jdbc:mysql://test.ourapp.com/ourapptest?user={userid}&password={password}!ServerType=29!QuoteChar=`]

Generic JDBC Driver Behavior: [No]

PreQEServerType: [JDBC (JNDI)]

PreQEServerName: [dev.ourapp.com/ourappdev]

PreQEDatabaseName: [com.mysql.jdbc.Driver]

UserId: [appuser]

==========================================================

I also tried to explitly change the servername connection property and the JDBC Connection String property to point our dev enviroment, but I got a "Database connection error" The username and password are the same on each system so its not a problem with the logon info..

 

[ArtieChoke]

I don't know about the JDBC connection, but it's probably similar to ODBC. One thing you need to do is to clear the database name from the query. Go to view SQL and you will probably see the table fully qualified with the db name. I'm not sure exactly where you remove it - go to database locations and select the table properties. Then you will either see an override name that is fully qualified or blank. If it is blank, enter the table name. If it is qualified, remove the db name and owner and it should work after that.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[keenanbr]

vidru sent me this reply when I asked similar question. It help me a great deal.

If you're using VB6, take a look at my FAQ on changing data sources at runtime: FAQ768-5374.

 

[keenanbr]

yoops, i see you already tried this. This worked perfectly for me but instead of

strServerOrDSNName = "dev.ourapp.com/appdev"

strDBNameOrPath = "com.mysql.jdbc.Driver;"
strUserID = "appuser"
strPassword = "apppassword"

i had

strServerOrDSNName = "MyDSN"

strDBNameOrPath = "MyDatabase"
strUserID = "appuser"
strPassword = "apppassword"

 

[WdnUlik2no]

Hi guys,

Sorry for the delay. I finally got it working thanks to all of the suggestions you guys made. I ended up figuring it out and what I had to do was to change the "Connection URL" property to point to the actual database I wanted to go to.

crxRpt.Database.Tables(1).ConnectionProperties("Connection URL") = "jdbc:mysql://dev.ourapp.com/appdev?user=<actual username>&password=<actual password>"

After I did this, the app changed the database at runtime as expected and didn't use the test DB in the .rpt file. I am sure that this isn't the only way to do it, but the first method that worked for me.

Thanks for all of your help guys!

 

[WdnUlik2no]

Guess I spoke too soon....

While I am able to switch DB's at runtime, doing this also removes all of my options set in the crystal report. In my .rpt file, I have "Select Distinct Records" checked in the report options window.

However when I connect to the database using the VB app, the report is no longer selcting distinct records which throws off my numbers.

Is there anyway to turn on the "Select Distinct Records" option in my application?

 

[keenanbr]

Dim crxApp As New CRAXDDRT.Application
Dim crxRpt As CRAXDDRT.Report

Set crxRpt = crxApp.OpenReport("MyReport.rpt")
crxRpt.EnableSelectDistinctRecords = True

 

[bmeza]

hi, im using cr 8.5 and vb6.0 and i have same problem ? and i try the solutions but still using the database defined in the .rpt file did you fine other solution?
Here its my code:
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

 

[WdnUlik2no]

bmeza,

yes I finally figured it out. For some reason it involved actually setting the SQL query in the VB code. I will post the code up here when I get my laptop in the house.

 

[WdnUlik2no]

Sorry for the long delay, I just have been REAL busy for the past few weeks. Here is what I did in my code to allow it to switch DB locations at run time. Like I said previously, it involved altering the Crystal report SQL query programmatically:

Private Sub inspector_Data_Report()
boolDebug = False
Viewer.Visible = False
Viewer2.Visible = False
On Error GoTo err:

strServerOrDSNName = ""



strDBNameOrPath = txtEnv.Text
strUserID = txtUsername.Text
strPassword = txtpassword.Text

'Open the report
Set crxrpt = crxApp.OpenReport("C:\APP WEB\INSPECTORS3.rpt")
crxrpt.Database.Tables(1).ConnectionProperties("Connection URL") = "jdbc:mysql://" & strDBNameOrPath & "?user=" & strUserID & "&password=" & strPassword
crxrpt.Database.Tables(1).SetLogOnInfo strServerOrDSNName, _
strDBNameOrPath, strUserID, strPassword
MsgBox crxrpt.Database.Tables(1).ConnectBufferString

'==============================
'DEBUG STUFF
'==============================
If boolDebug = True Then
Form2.Show
Dim i As Integer
i = 0
Dim strItem As String
MsgBox crxrpt.Database.Tables(1).ConnectionProperties.Count
While (i < crxrpt.Database.Tables(1).ConnectionProperties.Count)
strItem = crxrpt.Database.Tables(1).ConnectionProperties.NameIDs(i)
If strItem = "Password" Then
MsgBox "****Can't Display Password****"
Form2.Text1.Text = Form2.Text1.Text & "****Can't Display Password****" & vbCrLf
Else
Debug.Print strItem & ": [" & crxrpt.Database.Tables(1).ConnectionProperties.Item(strItem) & "]"
Form2.Text1.Text = Form2.Text1.Text & strItem & ": [" & crxrpt.Database.Tables(1).ConnectionProperties.Item(strItem) & "]" + vbCrLf
'MsgBox crxRpt.Database.Tables(1).ConnectionProperties.Item(strItem), vbOKOnly, strItem
End If
i = i + 1
Wend
End If
'===========================
'END DEBUG
'===========================

Dim Loctemp As Variant //not needed, just was debugging
Dim Namtemp As Variant //not needed, just was debugging
Dim x As Integer
'This removes the schema from the Database Table's Location property ====> THANKS vidru!
Set crxTables = crxrpt.Database.Tables
For Each crxTable In crxTables
With crxTable
.Name = .Name
End With

Next
'Loop through the Report's Sections to find any subreports, _
and change them as well
'============================
'Do not delete Start
'============================
Set crxSections = crxrpt.Sections

For i = 1 To crxSections.Count

Set crxSection = crxSections(i)

For j = 1 To crxSection.ReportObjects.Count

If crxSection.ReportObjects(j).Kind = crSubreportObject Then
Set crxSubreportObject = crxSection.ReportObjects(j)

'Open the subreport, and treat like any other report
Set crxSubReport = crxSubreportObject.OpenSubreport
'*****************************************
Set crxTables = crxSubReport.Database.Tables

For Each crxTable In crxTables
With crxTable
.SetLogOnInfo strServerOrDSNName, _
strDBNameOrPath, strUserID, strPassword
.Name = .Name
'MsgBox "Name", vbOKOnly, "SUBREPORT"
'MsgBox .Parent.Parent.EnableSelectDistinctRecords
End With
Next
'****************************************
End If

Next j

Next i

'===========================================
'do not delete end
'=========================================



MsgBox crxrpt.EnableSelectDistinctRecords 'This makes sure distinct records is enabled.
MsgBox crxrpt.SQLQueryString 'All I am doing right here is viewing the current SQL Query string defined in the Report.

'The command below alters this string so it is able to read from another location set at run time. I discovered that Crystal reports was adding "appdev" after the table name in the FROM clause. I figured out that the "appdev" in "FROM appdev.'{TABLE_NAME}'" needed to be removed. It should just be FROM '{TABLE_NAME}************************************'
crxrpt.SQLQueryString = "SELECT DISTINCT `INSPECTIONS`.`INSPECTION_OBJECT_ID`, `INSPECTIONS`.`COMMENTS`, `INSPECTION_CHANGES`.`USERID`, `USERS`.`FIRSTNAME`, `USERS`.`LASTNAME`, `GENERATORS`.`BUSINESS_NAME`, `INSPECTIONS`.`INSPECTION_TIME`, `USERS`.`ROLE_OBJECT_ID`, `USERS`.`USERID`, `INSPECTIONS`.`APPROVAL_STATUS`, `GENERATORS`.`BUS_ADDRESS_1`, `GENERATORS`.`BUS_CITY`, `GENERATORS`.`BUS_STATE`, `GENERATORS`.`BUS_ZIP`, `GENERATORS`.`BUS_ZIP_4`, `GENERATORS`.`BUSINESS_PHONE`, `GENERATORS`.`ZONE_OBJECT_ID`, `GENERATORS`.`BUS_ADDRESS_2` FROM ((`INSPECTIONS` INNER JOIN `INSPECTION_CHANGES` ON `INSPECTIONS`.`INSPECTION_OBJECT_ID`=`INSPECTION_CHANGES`.`INSPECTION_OBJECT_ID`) INNER JOIN `GENERATORS` ON `INSPECTIONS`.`GENERATOR_OBJECT_ID`=`GENERATORS`.`GENERATOR_OBJECT_ID`) INNER JOIN `USERS` ON `INSPECTION_CHANGES`.`USERID`=`USERS`.`USERID` WHERE `USERS`.`ROLE_OBJECT_ID`=6 ORDER BY `INSPECTION_CHANGES`.`USERID`"

MsgBox crxrpt.SQLQueryString
Viewer.ReportSource = crxrpt
Viewer.ViewReport


'Viewer.Zoom (400)
err:
If err.Number = 0 Then
MsgBox "Success!!"
Else
'Still figuring out my errors here. I just have a generic error now.
MsgBox err.Description & " Please check your logon credentials", vbCritical, "Error: " & err.Number
'Resume Next
End If
End Sub

 

[vidru]

'This removes the schema from the Database Table's Location property ====> THANKS vidru!

This might sound silly, but that just made my day.

-dave

 

[petermeachem]

I have always used TTX based crystal reports which don't suffer any of the 'change database' type problems which seem to crop up from time to time. Seems to be a pretty straightforward method to use.