sql server - Updating Data Provider of Crystal Reports

Question

I have created number of Crystal Reports in My VB.NET Project getting data from Microsoft Access 2007 (.accdb) Database.

Now, I am updating My Application with Microsoft SQL Server (.mdf)Database.

How Can I Update the Data Source From Access Files To MDF Files For All of My Crystal Reports?

Thanks.

Answer 1

Unless someone has a quicker / simpler solution, here is the approach I use to get crystal reports working with MS Access in Visual Studio 2010. It is slightly more involved than the normal method of selecting a datasource, but it offers more control, is database agnostic and works where the other methods sometimes fail (especially with MS Access in my experience).

I'll detail the process of getting the reports working from start to finish (because the entire process may be useful for others) but will highlight the bits that may be of use to you.

1) Install Crystal Reports form the SAP site. Make sure you download the exe file, NOT the msi one (it doesn't work):

http://scn.sap.com/docs/DOC-7824

2) Change the Target Framework of your app to .Net Framework 4 (NOT client). The Crystal stuff is not included in the basic profile.

3) Add a reference to the Crystal libraries (Crystal Reports for .NET)

4) Add a form, on that form place a Crystal Report Viewer control (from the Reporting section of the Toolbox):

5) Add a CrystalReportDocument and assign it to the CrystalReportViewer control:

To actually populate the report with data you do the following:

1) Output your report schema by executing code that takes your report datatable (from a gateway) and exports the schema. For example:

<TestMethod()>
Public Sub SchemaTest()
    Dim dataSet As DataSet = StaticDataGateway.AccountingIncomeTotals
    dataSet.WriteXmlSchema("I:Myschema.xml")
End Sub

If you already have your reports created you will probably not need this. Same if you can write the xml by hand (easy once you have a template to work from)

2) You then load this into your report to get the fields which you can then manipulate to produce your report. To do this you right click on database fields in the Report Field Explorer:

3) From here you select Database Expert and choose ADO.net (xml). The logical choice of Access/Excel DAO actually does not work at all in VS 2010. Select your schema file and load it, then double click on the Table1 to populate the Selected Tables view:

4) Now that you have a schema loaded, you will be able to see fields in the fields dropdown which you can drag n drop onto your report:

5) Finally you load a dataset into your report by executing code in the on load event of your form that you placed the reportviewer control on:

i) Make sure you output a dataset (not datatable) from your gateway (or wherever):

Public Shared Function AccountingIncomeTotals() As DataSet
    Dim dataSet As New DataSet
    Dim dataTable As DataTable = Database.GetDataTable(GetCommand(DBC.Reporting.SPs.AccountingIncomeTotals))
    dataSet.Tables.Add(dataTable)
    Return dataSet
End Function

ii) Then assign it to the datasource on the report:

Private Sub AccountingIncomeTotals_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    AccountingIncomeTotalsReport1.SetDataSource(StaticDataGateway.AccountingIncomeTotals)
End Sub

The above section will probably be especially interesting to you because it allows you to directly assign the datasource to the report without having to use the crystal select Data Sources UI panel.

There's one other thing required, you need to add "useLegacyV2RuntimeActivationPolicy" To the app.config file:

http://www.codeproject.com/Questions/390643/Error-When-use-of-crystal-report-sap-crystal-13-an

I hope this helps, sorry it's so involved, if you have any questions please ask