Report Location to Sql Server 1

[RohanP]

In this example the location property of the DatabaseTable Object for each table in Report is set to MDB File.
******************
For Each crxDatabaseTable In Report.Database.Tables
crxDatabaseTable.Location = App.Path & "\xtreme. mdb"
Next crxDatabaseTable
******************
I want to set the location to different Database residing on Sql Server. What Value should I assign to Location Property for it?

The Report is designed with DB1, DB2 contains same tables & relationships. I want to open the report using DB2's Tables. Is it the right way I am going abt?

I am new to RDC pls help.. ______________________________
- Regards Rohan (India,Mumbai)

 

[LV]

You may try this:

' you might not need the first line of code with LogOnServer
Report.Database.LogOnServer "pdssql.dll", "DB2ServerName", "DB2DatabaseName", "DB2UserName", "DB2Password"
For Each crxDatabaseTable In Report.Database.Tables
crxDatabaseTable.SetLogOnInfo "DB2ServerName", "DB2DatabaseName", "DB2UserName", "DB2Password"
Next crxDatabaseTable

 

[RohanP]

I have tried this

Report.Database.LogOnServer "pdssql.dll", "Rohan", "Exports1", "sa"

'Use a For Each loop to change the location of each
'DatabaseTable in the Reports DatabaseTable Collection
For Each crxDatabaseTable In Report.Database.Tables
crxDatabaseTable.SetLogOnInfo "Rohan", "Exports1", "sa"
Next crxDatabaseTable

But it is not changing the database I have set while making the report in Report Designer.

I even tried
crxApplication.LogOnServer "pdssql.dll", "Rohan", "Exports1", "sa"

but nothing is helping me out.
I think I will lose this project if i am not able to change the db location, pls help

______________________________
- Regards Rohan (India,Mumbai)

 

[LV]

Make sure your report doesn't have saved data in it, you need to include this line right after you create Report object:

Report.DiscardSavedData

Also, include this line in you loop, debug it and check if your new db connection is good, this can give you an idea if you are connecting to a new database at all:

For Each crxDatabaseTable In Report.Database.Tables
crxDatabaseTable.SetLogOnInfo "Rohan", "Exports1", "sa"
' this line will write a boolean
Response.Write(crxDatabaseTable.TestConnectivity & &quot;<br>&quot
Next crxDatabaseTable

 

[RohanP]

I Tried the below code but was not opening Exports1 DB but showing Exports DB's data . But When I added this line of code (Report.SQLQueryString = &quot;Select * From Customer&quot as there is only 1 table in the dummy report,
Between code Report.DiscardSavedData and Report.Database.LogOnServer the report is displayed with Exports1 DB. I am getting what I want but do I have to write the SQLQueryString for all the reports I want to change the Database? Pls let me know a feasible solution..

When I Unload the Form which has (Report.SQLQueryString = &quot;Select * From Customer&quot code. VB terminates itself. One more disaster for in the way.

Pls Help me out

-----Code I Wrote----
Private Sub Form_Load()
' Declare a DatabaseTable Object
Dim crxDatabaseTable As CRAXDRT.DatabaseTable

' Declare a Report object to set to the subeport
Dim crxSubreport As CRAXDRT.Report

' Open the report
Set Report = crxApplication.OpenReport(&quot;f:\rdc\cust.rpt&quot;, 1)


Report.DiscardSavedData
Report.Database.LogOnServer &quot;pdssql.dll&quot;, &quot;Rohan&quot;, &quot;Exports1&quot;, &quot;sa&quot;

'Use a For Each loop to change the location of each
'DatabaseTable in the Reports DatabaseTable Collection
For Each crxDatabaseTable In Report.Database.Tables
crxDatabaseTable.SetLogOnInfo &quot;Rohan&quot;, &quot;Exports1&quot;, &quot;sa&quot;
Next crxDatabaseTable

End Sub ______________________________
- Regards Rohan (India,Mumbai)

 

[LV]

Did you try this:

For Each crxDatabaseTable In Report.Database.Tables
crxDatabaseTable.SetLogOnInfo &quot;Rohan&quot;, &quot;Exports1&quot;, &quot;sa&quot;
msgbox(crxDatabaseTable.TestConnectivity)
Next crxDatabaseTable

Does it say false or true?

 

[RohanP]

I tried this out
MsgBox crxDatabaseTable.TestConnectivity) & &quot; &quot; & crxDatabaseTable.Name

Every Table is returning True Value I asume that means connectivity is successful. But database is still not changing. But If I pass SqlString to the report object it is working fine. Giving Sql Statement from frontend for all reports is not a feasible solution.

pls help. ______________________________
- Regards Rohan (India,Mumbai)

 

[LV]

I'll try to replicate it; the only thing I can think about now is to check if the original report has no &quot;Save Data with report&quot; option checked. Open report in the Crystal designer an check it (File...Save Data with Report must be unchecked).

 

[RohanP]

I unchecked the Save Data with Report option but still the report is not refereshing with specified Database.

is it so difficult to get this solution? ______________________________
- Regards Rohan (India,Mumbai)

 

[kenhamady]

I assume that &quot;Rohan&quot; is the user name, so shouldn't the order of the arguments be:

Report.Database.LogOnServer &quot;pdssql.dll&quot;, &quot;Exports1&quot;, &quot;Rohan&quot;, &quot;sa&quot; Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[RohanP]

Server Name - Rohan
username - sa
dbname - exports1




______________________________
- Regards Rohan (India,Mumbai)

 

[BFAGG]

Rohan,

Have you tried this?

Open the report in Crystal Reports. Do to the menu and select Database -> set location. In this window, ensure that the database name and owner shown in the lower panel for each table is for the correct database. Use the set location function to correct this if required.

Hope this helps

Bill.