Crystal Reports

How Do I Set the Location of database tables in crystal reports with VB?
How Do I use SetLogOnInfo in crystal reports with VB?
How Do I pass LogOn Information/credentials like database name,user name, password and DSN to a Crystal Report from VB?

 

Introduction

The article shows you how to loop through each Database Table in the Database Tables collection of a Crystal report and then set the location of the database file for each table. Also shows you how to pass DSN name and login info If you are using a database like SQLServer.

The article demonstrates using CRAXDRT object Library to achieve this.

 

Library References

Crystal Reports software comes with Crystal Reports ActiveX Designer Run Time Library which provides the necessory objects to do this. Though I've used version 9.0 objects in the article, it should work with version 8/8.5/9/10/XI without any trouble, I think. So, before getting started, you need to reference this object library to your VB Project.

Decalrations

First, let's declare the Object variables to hold the Crystal Application, Report, Database, Tables collection and Table objects

    Dim CrxApp As CRAXDRT.Application
    Dim CrxRep As CRAXDRT.Report
    Dim crxDatabase As CRAXDRT.Database
    Dim crxDatabaseTables As CRAXDRT.DatabaseTables
    Dim crxDatabaseTable As CRAXDRT.DatabaseTable

 

Instantiate the Application object and open the Crystal Report from the disk

    Set CrxApp = New CRAXDRT.Application
    Set CrxRep = CrxApp.OpenReport("H:\test\crtests\Report1.rpt")

 

Now, set our Database object to the Report object's Database object

    Set crxDatabase = CrxRep.Database

 

And then, set our DatabaseTables object to the Database object's Tables object

    Set crxDatabaseTables = crxDatabase.Tables

 

Having obtained a reference to all the table objects in the report, now we loop through each DatabaseTable object in the DatabaseTables collection and then set the location of the database file for each table.
If the source is a Native connection or ODBC connection to a PC or SQL database, then we use the SetLogOnInfo method to set the logon credentials.

SetLogOnInfo takes 4 parameters. First one is the DSN name for ODBC connections and Servername for Native connection to SQL database, Second is the database name (like northwind), third is username and the last one is the password.

    For Each crxDatabaseTable In crxDatabaseTables
        'If you are using physical path of the access database file (Native connection to PC database)
        crxDatabaseTable.Location = App.Path & "\xtremelite.mdb"

        'Use next line, if you are using Native connection to SQL database
        'crxDatabaseTable.SetLogOnInfo "servername", "databasename", "userid", "password"

        'Use next line, if you are using ODBC connection to a PC or SQL database
        'crxDatabaseTable.SetLogOnInfo "ODBC_DSN", "databasename", "userid", "password"
    Next crxDatabaseTable

 

Now, preview the Report

    'Make sure you add a Crystal Report Viewer Control to you form
    CRViewer91.ReportSource = CrxRep
    CRViewer91.ViewReport

 

Finally, release the references.

    Set crxDatabase = Nothing
    Set crxDatabaseTable = Nothing
    Set crxDatabaseTables = Nothing
    Set crxReport = Nothing
    Set crxApplication = Nothing

 

Full Code


'Add a command button and a Crystal Report Viewer Control to you form
'Reference Crystal Reports 8/8.5/9/10 ActiveX Designer Run time Library
'Paste the following code into the form code window
'Change the path "C:\crtests\Report1.rpt" to suit yours

Private Sub Command1_Click()
    Dim CrxApp As CRAXDRT.Application
    Dim CrxRep As CRAXDRT.Report
    Dim crxDatabase As CRAXDRT.Database
    Dim crxDatabaseTables As CRAXDRT.DatabaseTables
    Dim crxDatabaseTable As CRAXDRT.DatabaseTable

    Set CrxApp = New CRAXDRT.Application
    Set CrxRep = CrxApp.OpenReport("H:\test\crtests\Report1.rpt")
    
    'Set your Database object to the Report object's Database object
    Set crxDatabase = CrxRep.Database
    
    'Set your DatabaseTables object to the Database object's Tables object
    Set crxDatabaseTables = crxDatabase.Tables
    
    'Loop through each DatabaseTable object in the DatabaseTables collection and then set the location
    'of the database file for each table
    
    For Each crxDatabaseTable In crxDatabaseTables
        'If you are using physical path of the access database file (Native connection to PC database)
        crxDatabaseTable.Location = App.Path & "\xtremelite.mdb"

        'Use next line, if you are using Native connection to SQL database
        'crxDatabaseTable.SetLogOnInfo "servername", "databasename", "userid", "password"

        'Use next line, if you are using ODBC connection to a PC or SQL database
        'crxDatabaseTable.SetLogOnInfo "ODBC_DSN", "databasename", "userid", "password"
    Next crxDatabaseTable
    
    
    CRViewer91.ReportSource = CrxRep
    CRViewer91.ViewReport
    
    Set crxDatabase = Nothing
    Set crxDatabaseTable = Nothing
    Set crxDatabaseTables = Nothing
    Set crxReport = Nothing
    Set crxApplication = Nothing

End Sub

Top

www.mahipalreddy.com
Terms and Conditions of Use
Copyright 2004 - 2006 Mahipal Padigela. All rights reserved.