Solved

One CrystalReport and many databases

Posted on 2000-04-13
3
168 Views
Last Modified: 2013-11-26
I have several MS Access databases with the same structure (same table names, same field names).
I have one Crystal report MyReport.rpt. How can I use this report for all databases?
1. I can bound this report to the Data control and change datacontrol.recordsource .
2. I can write many same reports and switch them in a run time.
3. Is there any other way? (DataFiles property of Crystal Report control? How can I use it?)
Limitations: VB-5,Crystal Report Control, Seagate Crystal Report 6,Windows95

0
Comment
Question by:Lom1
  • 2
3 Comments
 
LVL 1

Accepted Solution

by:
sramkris earned 30 total points
ID: 2712873
Hi
The best way to do these reports which connect to diff databases are creating a template which does not connect to any database at design time.

for the recordset to be passed to Crystal at runtime u have to follow the following steps

1. When designing the report you have to select custom reports sqlodbc.
2. This will pop up another screen where you have to select Active Data (Field Definitions Only).
3. This will pop another screen called Select data source (data Definition) click on the new option Button
4. This will bring another screen where you have to enter the fields and their data types which are to be shown on the report.Save the file (extension .ttx)
and use that to design your report.
    The report will be a dumb report because you are not connected to any data source at this moment.

The requirement is that you have to use vb6.0 with Crystal 7.0 or you can download the report designer component from the seagate web site for free.

In Vb under the project menu you will have add crystal report click that.

This will bring the designer up say import report and bring the report which you have created above.

Then another screen will pop up which will ask if you need the viewer and if you want it to be the starting form.
Say yes to the viewer wanting option and say no to make the viewer the startup object.

For more than one report you can say no to both the options as you can use the same viewer for all the reports.

The declaration will have something like this

Dim Report As New CrystalReport1
(The report which you have imported
You can give the name you want to give)

Dim cnn1 As New ADODB.Connection
Dim rstTest As ADODB.Recordset
Dim rstUser As ADODB.Recordset
Dim strCnn As String
Dim varDate As Variant
Dim sql As String

Now connect to the database
Private Sub Form_Load()
   ' Open connection.
   strCnn = "Data Source=blackhawk;User=dba;Password=chelios; " 
   Set cnn1 = New ADODB.Connection
   cnn1.Open strCnn

Fire the sql query
     
sql = "select us.user_guid,us.user_last_name, us.user_first_name,us.user_middle_name,ir.lilevel,ir.listatus," & _
    " ir.palevel,ir.pastatus,ir.swlevel,ir.swstatus,ir.wclevel,ir.wcstatus,ir.scon,ir.pclevel,ir.pcstatus" & _
      " from blackhawk01.dba.users us,blackhawk01.dba.ir_history ir where us.user_guid = ir.student_guid " 

( Open the recordset and dump the recorset with the result of the query)

   Set rstUser = New ADODB.Recordset
   rstUser.CursorType = adOpenKeyset
   rstUser.LockType = adLockOptimistic
   rstUser.Open sql, cnn1, dOpenKeyset, adLockOptimistic

Screen.MousePointer = vbHourglass
(the report source is the report which is created)

CRViewer1.ReportSource = Report
(Set the datasource of the report with the recordset you have created)

Report.Database.SetDataSource rstUser
CRViewer1.ViewReport
Screen.MousePointer = vbDefault


I hope this solves ur problem

0
 

Author Comment

by:Lom1
ID: 2712959
I was stupid.
I can switch the databases with one line of code.
CristalReportControl.dataFiles(0)= “C:\…\DatabaseName.mdb”

CristalReportControl.Action=1

My problem was I saved data with Report when I created one and didn’t refresh it.
But your answer is accepted. I will use it in a future.  Thanks a lot.
0
 

Author Comment

by:Lom1
ID: 2712961
I was stupid.
I can switch the databases with one line of code.
CrystalReportControl.dataFiles(0)= “C:\…\DatabaseName.mdb”

CrystalReportControl.Action=1

My problem was I saved data with Report when I created one and didn’t refresh it.
But your answer is accepted. I will use it in a future.  Thanks a lot.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question