Data Access for the Developer using Crystal Reports.

Data Access for the Developer using Crystal Reports.

As there are a number of differing methods of using CR as a developer, I have outlined the procedure for establishing data access for the following:

Using the Report Designer Component (craxdrt.dll) with Visual Basic

Having set up your Report Object as follows:
Dim CRXReport As CRAXDRT.Report
Set CRXReport = New CrystalReport1
Or
Dim CRXReport As New CrystalReport1

1) with Datafile data source (e.g. Btrieve, Paradox, dBase, MSAccess etc.)

Set CRXTables = CRXReport.Database.Tables
Set CRXTable = CRXTables(1)
CRXTable.Location = ôC:\Program Files\Seagate Software\Crystal Reports\Samples\Databases\Xtreme.mdbö

2) with SQL Native connection data source (e.g. MS SQL Server)

Set CRXTables = CRXReport.Database.Tables
Set CRXTable = CRXTables.Item(1)
CRXTable.SetLogonInfo ôTheSQLServerö, öpubsö, öAdminö, öAdminö
Or
CRXTable.SetLogonInfo<servername>,<databasename>,<userid>,<password>

3) with ODBC connection

Set CRXTables = CRXReport.Database.Tables
Set CRXTable = CRXTables.Item(1)
CRXTable.SetLogonInfo ôXtreme Sample Databaseö,öXtremeö,öAdminö,öö
Or
CRXTable.SetLogonInfo<ODBC_DSN>,<databasename>,<userid>,<password>


4) ADO Recordset

Having first defined your recordset as follows:
Dim rs As New ADOR.Recordset
rs.Open ôSelect * From Customerö, ôXtreme Sample Databaseö
Then
Set CRXTables = CRXReport.Database.Tables
Set CRXTable = CRXTables.Item(1)
CRXTable.SetDataSource rs
Or
CRXTable.SetDataSource <recordset>

5) with OLEDB Providers (e.g. MS Access)

Dim cnn1 As New ADODB.Connection
Dim datcmd1 As New ADODB.Command
Dim strCnn As String
Set cnn1 = New ADODB.Connection
strCnn = ôProvider=MSDASQL;DSN=Xtreme Sample Database;UID=Admin;PWD=ö
cnn1.Open strCnn
Set datcmd1 = New ADODB.Command
Set datcmd1.ActiveConnection = cnn1
datcmd1.CommandText = ôCustomerö
datcmd1.CommandType = adCmdTable
CRXReport.Database.AddOLECommand cnn1,datcmd1
Or
CRXReport.Database.AddOLEDBSource ôDSN=Xtreme Sample Database;DBQ=xtreme.mdb;DefaultDir=c:\Program Files\Seagate Software\Crystal Reports\Samples\Databases;Driver=odbcjt32.dll;DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;ö,öCustomerö

Or for MS SQL Server

Provider=SQL_OLEDB;Server=TechTest;Database=Pubs;UID=Admin;PWD=sa

NOTE the reference to SQL_OLEDB above is not the correct entry - remove the underscore in use - it's only there because I keep getting an emoticon for LOL

Or for Oracle

Provider=MSDAORA; PWD=password ; User ID=Admin;Datasource=TheOracleServer

Using the Report Designer Component (craxdrt.dll) with Visual Interdev

Having created your Application and Report objects as follows:

If Not IsObject (session("oApp")) Then
Set session("oApp") = Server.CreateObject("CrystalRuntime.Application")
End If

Path = Request.ServerVariables("PATH_TRANSLATED")
While (Right(Path, 1) <> "\" And Len(Path) <> 0)
iLen = Len(Path) - 1
Path = Left(Path, iLen)
Wend

'This "While/Wend" loop is used to determine the physical path (eg: C:\) to the 'Crystal Report file by translating the URL virtual path (eg: http://Domain/Dir)

If IsObject(session("oRpt")) then
Set session("oRpt") = nothing
End if

Set session("oRpt") = session("oApp").OpenReport(path & reportname, 1)

You can then execute the SetLogonInfo method as follows:

userid = "Seagate"
password = ""
set crtable = session("oRpt").Database.Tables.Item(1)
crtable.SetLogonInfo "Automation", "pubs", cstr(userid), cstr(password)