Opening and refreshing a Crystal Report through Access

[AzimuthX]

I would like to...

- open the report (how to connect through ODBC?)
- refresh the report (logon to a database)
- set parameters

I am using Crystal Report 8.0 and Microsoft Access 2000

 

[vidru]

Here are the sample apps:

http://support.businessobjects.com/communityCS/FilesAndUpdates/rdc_vba.exe.asp

You'll need the Developer edition of CR 8.

-dave

 

[AzimuthX]

I have already downloaded that... I have no problems opening the specified report (but not through ODBC). How can I be able to refresh the report to the database (ie AS400), because when I click on Refresh from the Form, nothing happens, I must click on the Refresh icon from CR. Then there are parameters to enter (Start of Range, and End of Range) and a password to supply.

 

[Turkbear]

Hi,
IIRC, as long as you have parameters that need to be entered, you will get that behavior everytime you use the CR refresh button. Even if you supply default values this will happen.

We do not allow refreshing ( we do not show the 'lightning bolt' button on the viewer) for that very reason; we ask that the user run the report again ( our setup enables this by using the browser's back button to return to the page that prompts for parameters..the old values are still there , so a click on the view button refreshes the data or new values can be chosen - it is set to refresh on every print).

 

[vidru]

If your report is already set up to use an ODBC DSN, then you need to call the LogOnServer method. Using the sample you downloaded, the following is all of the necessary code to logon and preview a report from an ODBC data source in the Image40_Click() event ('Refresh Report'):
[tt]
Set crxReport = Nothing
Set crxReport = crxApplication.OpenReport(MyReportFile)
crxReport.Database.LogOnServer "p2sodbc.dll", "DSN_Name", "DB_Name", "UserID", "Password"
Me!CRViewer1.ReportSource = crxReport
Me!CRViewer1.ViewReport
[/tt]
The same code (minus the first line) would also go into the Image35_Click() event ('View').

To set parameters within the code as opposed to the Crystal parameter window:
[tt]
'Set a String parameter
crxReport.ParameterFields.GetItemByName("Country").AddCurrentValue "Argentina"
'Set a Date Range parameter
crxReport.ParameterFields.GetItemByName("DateRange").AddCurrentRange CDate("6/1/04"), CDate("6/29/04"), 3
[/tt]
-dave

 

[AzimuthX]

I am having difficulties with this line...

Configuration Error.

File could not be opened because the library "C:\Program Files\IBM\Client Access\Shared\cwbodbc.dll" cannot be found.

crxReport.Database.LogOnServer "C:\Program Files\IBM\Client Access\Shared\cwbodbc.dll", "AS400 SYSTEM2", "AS400HOST", "USER", "PASSWORD"

I checked under Administrative/ODBC/Drivers, the AS400 uses this .dll

 

[AzimuthX]

Even if I copy the DLL to the folder that the MS-Access file is in... it still says it can not be found.

 

[vidru]

I've never reported on AS400, so from this point on, the help I can try to give you is limited.

Open up the report in the Crystal designer, and go to Database>Convert Database Driver. What driver is it using (it'll be grayed out)? Whatever it says, that's the first argument you should be passing to the LogOnServer method. You don't need the path (or at least you shouldn't).

-dave

 

[AzimuthX]

It is pdsodbc.dll ... hmm?

My main problem is logging onto the database, I have the Server Name, Database Name, Login & Password, just having difficulties with the .dll

 

[vidru]

What happens when you use pass pdsodbc.dll (or p2sodbc.dll)? Are you getting an error?

As far as I know, all ODBC connections with Crystal connect through pdsodbc.dll or p2sodbc.dll. It then communicates with the driver that was used when the DSN was created to access the server. If you want to learn more about it, check out this document:

http://support.businessobjects.com/communityCS/TechnicalPapers/cr_odbc_architecture.pdf.asp

-dave

 

[AzimuthX]

I finally got it to not give an error when logging into the AS400 system. This is a seperate form... I can view the report locally, but I have difficulties refreshing with the parameters. I get a type mismatch error, I am not even sure if this is the right code to refresh the report.

 

[AzimuthX]

How do I use the "RefreshButtonClicked" event? It won't refresh properly, it will when I click on the refresh button directly from the Crystal Report Viewer. I have noticed there is the event "RefreshButtonClicked", would this be what I am looking for? Good news is that when I click on the Refresh button, it no longer asks for login information.

Here is the code.

Private Sub cmdTest_Click()
Dim crxReport
Dim crxApplication

Set crxReport = Nothing
Set crxApplication = New craxdrt.Application
Set crxReport = New craxdrt.Report

Set crxReport = crxApplication.OpenReport("reportfile")
crxReport.Database.LogOnServer "pdsodbc.dll", "AS400 SYSTEM 2", "AS400HOST", "user", "password"
crxReport.ReadRecords

Me!cviewer.ReportSource = crxReport
Me!cviewer.ViewReport
End Sub

 

[family2003]

I have some reports that are already created in infoagent software, the queries also are already created. I would like to create the same reports in crystal reports 8.5..I know that I will have to copy paste the queries in crystal reports..My problem is that I cann retrieve any data when I connect to the SQL server over the ODBC data sourc..I have no problems logging to the server and I know for sure that I have rights retrieving/writing from/to the server...My second question is that how can I create queries in crystal reports...As I mentioned I may have to copy paste but I am not sure where is that will be done in Crystal reports and I am supposed to create the design of the report first or the queries first....I will really appreciate it if I can get an answer very soon since this is needed and I am new to the software....