How to change the report's DataSource on runtime 1

[ndaru]

SITUATION:

Supposed we've created a report using Crystal Report Designer. To ease the design-time process, we connected the report to our Access Database (.mdb) using ODBC connection (the pull method). Now we're ready to show the report from our application, but this time we'll connect the report to our runtime-created RecordSet (the push method).

' BEGIN OF CODE

Dim cnn As ADODB.Connection
Dim rst AS New ADODB.RecordSet
Dim dbPath as String
Dim rpt As New CrystalReport1 'supposed this is the report

dbPath = "C:\DATA\Northwind.mdb" 'this is just an example

' Runtime Connection
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";Persist Security Info=False"
cnn.Open
cnn.CursorLocation = adUseClient

' Runtime RecordSet
rst.Open "SELECT * FROM Products", cnn, adOpenStatic, adLockReadOnly, adCmdText

' Feed the RecordSet to the report's DataSource.
' Of course the Recordset's field names and types must match the ones on the report
rpt.Database.SetDataSource rst

' View the report here
' (this where we put the code to view the report)

' Close the RecordSet and Connection
rst.Close
cnn.Close

' END OF CODE

After calling the SetDataSource method, we expect that the report will show the content from the runtime-created Recordset. What if not?

SOLUTION:

It seems that the SetDataSource method doesn't work on ODBC-connected report, but it does work on ADO-connected report. To change the report connection type do the following steps:

1. Open the report.
2. Right-click mouse on any blank space in the report design.
3. Choose Database / Convert Database Driver...
4. Check on "Convert database driver when reading data next time"
5. Pick "Pdsmon.dll (ADO, RDO, DAO) from the "To:" combobox.
6. Click OK. This will show the "Select Data Source" dialog.
7. Check on "ADO and OLE DB". Fill the connection string with something like this:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DATA\Northwind.mdb;Persist Security Info=False"
8. Click OK. This will show the "Select Recordset" dialog.
9. Pick your view or table from the "Object" combobox.
10. Click OK. If all goes well, you'll get "The database is up to date" message.

Now, re-run your code. This time the report should show the content from the runtime-created Recordset.

That's all. Good luck.

 

[ndaru]

Almost forgot, you must clear the report's saved data before changing the report's DataSource:

rpt.DiscardSavedData
rpt.Database.SetDataSource rst

If not, you'll get the same report's content regardless the RecordSet's content.