Well, in the case of a command object, Crystal is using an OLEDB connection (whether or not that means it is an ADO recordset is, perhaps, a matter of interpretation). But with a command object, you define, within Crystal itself, a query. So, Crystal has to have connectivity to the database. And, as far as I know (and I could be wrong here), the sql statement in the command object itself is not modifiable at runtime. You can, of course, use parameters to make the sql statement flexible, but items like the From clause are set in stone (I think) and the WHERE clause, while somewhat flexible, won't be highly dynamic.
Not so, however, when using an ADO recordset, created in vb, to populate a Crystal report. The basic idea here is that you create a report in Crystal using the Active Data driver and either supplying a ttx file (data definition file) or a sql statement as the basis for building the report (i.e, the sql statement provides a recordset at design time that will you allow you to create the report). But at runtime, the report itself has no connectivity to the database and simply is a recipient of a recordset handed to it by vb. So vb does the query, gets an ADO recordset, and pushes it into the Crystal report.
The primary advantages are that the sql can be entirely dynamic. Based on user input, the vb app can get the recordset from Server A or Server B (or C etc., or Database X or Database Y). The Where clause can be as dynamic as necessary, based on vb logic. And all connectivity (logging onto the database, etc.) is handled solely by your ADO connection in vb. The only limitation is that the schema of the recordset passed at runtime has to match the schema used to create the report. That means that the the field list has to be identical in terms of name, order, and data types.
Below is sample code that illustrates the use of an ADO-based Crystal report:
Form frmMain
----------------------
Option Explicit
Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim strUser As String
Dim strPswd As String
Dim strDatabase As String
Dim strServer As String
Dim crApp As New CRAXDRT.Application
Public crRpt As CRAXDRT.Report
Const cSalesOrders As String = "SalesOrders.rpt"
Private Sub Form_Load()
'This application uses an ADO 2.5 connection to the Northwind database
'in SQL Server. Use appropriate UserNames and Passwords for your connection
'Declare variables
Dim strConnection As String
Dim strSQL As String
'the server, database, and logon info are hardcoded for this illustration
strUser = "green"
strPswd = "password"
strDatabase = "Northwind"
strServer = "dell-laptop"
'Build the connection string
strConnection = "Provider=SQLOLEDB.1;Persist Security Info=False;"
strConnection = strConnection & "User ID=" & strUser & ";"
strConnection = strConnection & "Password=" & strPswd & ";"
strConnection = strConnection & "Initial Catalog=" & strDatabase & ";"
strConnection = strConnection & "Data Source=" & strServer
'Open the connection
cn.ConnectionString = strConnection
cn.Open
'Build the sql statement that will allow us to populate the
'drop down for the user interface
strSQL = "Select Distinct Country from Customers"
Set rs = New ADODB.Recordset
rs.Open strSQL, cn, adOpenKeyset, adLockReadOnly
'Populate the combobox
cboCountry.Text = "USA" 'default value
If Not rs.BOF Then
rs.MoveFirst
Do Until rs.EOF
cboCountry.AddItem rs.Fields(0).Value
rs.MoveNext
Loop
End If
'Cleanup
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
Private Sub cmdPreview_Click()
'Now we need to get an ado recordset that corresponds to the
'schema used to create the report and then pass the ado recordset
'to the report.
Dim rs As ADODB.Recordset
Dim strConn As String
Dim strSQL As String
Dim strCountry As String
'Crystal objects
Dim crDB As CRAXDRT.Database
Dim crTable As CRAXDRT.DatabaseTable
strCountry = cboCountry.Text
'Now Build a valid SQL Statement with strCountry as a dynamic value
strSQL = "SELECT "
strSQL = strSQL & "c.CustomerID, c.CompanyName, c.Region, c.Country, c.Phone, "
strSQL = strSQL & "c.ContactName, c.ContactTitle, "
strSQL = strSQL & "
rderID,
rderDate, o.ShippedDate, o.RequiredDate, "
strSQL = strSQL & "d.ProductID, d.UnitPrice, d.Quantity, d.Discount, "
strSQL = strSQL & "p.ProductName , p.ProductID "
strSQL = strSQL & "From "
strSQL = strSQL & "Customers c INNER JOIN Orders o "
strSQL = strSQL & "ON c.CustomerID = o.CustomerID "
strSQL = strSQL & "INNER JOIN [Order Details] d "
strSQL = strSQL & "ON
rderID = d.OrderID "
strSQL = strSQL & "INNER JOIN Products p "
strSQL = strSQL & "ON d.ProductID = p.ProductID "
strSQL = strSQL & "Where "
strSQL = strSQL & "c.Country = '" & strCountry & "'"
'Now establish the ado connection, using the module level
'variables for logon info
strConn = "Provider=SQLOLEDB.1;Persist Security Info=False;"
strConn = strConn & "User ID=" & strUser & ";"
strConn = strConn & "Password=" & strPswd & ";"
strConn = strConn & "Initial Catalog=" & strDatabase & ";"
strConn = strConn & "Data Source=" & strServer
'Open the ado connection
cn.ConnectionString = strConn
cn.Open
'Create the ado recordset
Set rs = New ADODB.Recordset
rs.Open strSQL, cn, adOpenKeyset, adLockReadOnly
'If we have records in the recordset
If Not rs.BOF Then
'Now Open report
Set crRpt = crApp.OpenReport(App.Path & "/" & cSalesOrders)
'Get rid of any data saved in the report
crRpt.DiscardSavedData
'Get the Database object
Set crDB = crRpt.Database
'an ado report should only have on table, so we can get it
'directly using the Item property of the Tables collection
Set crTable = crDB.Tables.Item(1)
'Pass the ado recordset to the database table
crTable.SetDataSource rs, 3
'Now read the records into the report before the rs goes out of scope
crRpt.ReadRecords
'and view the report
frmViewer.Show vbModal
Else
MsgBox "No Data For Report"
Exit Sub
End If
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
--------------------------------
Form frmViewer
--------------
Private Sub Form_Load()
'Assign the report from the previous form to the viewer
CRViewer91.ReportSource = frmMain.crRpt
'Set the magnification factor
CRViewer91.Zoom 100
'Maximize the form
Me.WindowState = 2
'View the report
CRViewer91.ViewReport
End Sub
Private Sub Form_Resize()
CRViewer91.Left = 0
CRViewer91.Top = 0
CRViewer91.Width = ScaleWidth
CRViewer91.Height = ScaleHeight
End Sub
