changing data sources does not work

[msilka]

I am developing in VB 6.0 with CR Version 9.
I have various SQL2000 databases with exactly the same structures.

Say I develop a report with Database1.

I then want to run that report using the same exact tables from Database2, Database3, etc.

Here is the code that was show to do it.

Dim crxtables As Object
Dim CPProperty As CRAXDRT.ConnectionProperty

Set MyReport = CrxApplication.OpenReport(ReportLocation)

Set crxtables = MyReport.Database.Tables

For Each Table In crxtables
Set CPProperty = Table.ConnectionProperties("Provider")
CPProperty.Value = "SQLOLEDB"
Set CPProperty = Table.ConnectionProperties("Data Source")
CPProperty.Value = "MYSQLSERVER"
Set CPProperty = Table.ConnectionProperties("Initial Catalog")
CPProperty.Value = "MYDB"
Set CPProperty = Table.ConnectionProperties("User ID")
CPProperty.Value = "USERNAME"

Next

MyReport.ViewReport

But, everytime I try to switch to a different database, the report only generates the information from the database it was created with.

Does anybody know a fix to this?

Thank you

 

[vidru]

Put in a debug.print statement at the end of that routine to see what the Location for each table says:

For Each Table In crxtables
Set CPProperty = Table.ConnectionProperties("Provider")
CPProperty.Value = "SQLOLEDB"
Set CPProperty = Table.ConnectionProperties("Data Source")
CPProperty.Value = "MYSQLSERVER"
Set CPProperty = Table.ConnectionProperties("Initial Catalog")
CPProperty.Value = "MYDB"
Set CPProperty = Table.ConnectionProperties("User ID")
CPProperty.Value = "USERNAME"

Debug.Print Table.Location
Next

If it returns it as "OldSchema.TableName" then that's where the problem is. I don't have CR9 yet (coming next week finally!), but with 8.5, I could reset the Location by overwriting the schema that was saved with the report with just the table name:

Table.Location = Table.Name

Like I said, I do this with 8.5, and I'm not up to date on the CR9 CRAXDRT object model, but this might get you closer to a solution.

-dave

 

[msilka]

Okay,

I did that and the results were positive, but:

If I run the report for Database1 then switch to Database2 and run the report the same information from Database1 is show. But, if I close the report for Database2 and view it again it now displays the correct information for Database2. I have tried this with the other databases with the same results.

 

[Turkbear]

Hi,
Looks like the data is being cached ..
Try setting the options to always refresh on print.

 

[msilka]

Okay,

I think I have figured it out now.

For Each Table In crxtables
Set CPProperty = Table.ConnectionProperties("Provider")
CPProperty.Value = "SQLOLEDB"
Set CPProperty = Table.ConnectionProperties("Data Source")
CPProperty.Value = "MYSQLSERVER"
Set CPProperty = Table.ConnectionProperties("Initial Catalog")
CPProperty.Value = "MYDB"
Set CPProperty = Table.ConnectionProperties("User ID")
CPProperty.Value = "USERNAME"

Table.Location = "MYDB" & ".dbo." & Table.Name
Next

Thank you for pointing me in the right direction VIDRU!!

NOTE: This will work for your report if you have the initial datasource as OLEDB. If it is a report with an initial datasource of ODBC, then the report is blank.
I will have to change all my ODBC reports to OLEDB.