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)
