How the code SetDatabaseLogon logon to Excel data source and refresh the crystal report property

[#1]

Hello,

I am using Crystal Report 2020. I have an Excel file as the data source.

I create my crystal report template base on this document

https://www.sap.com/documents/2020/11/a046b61d-bd7d-0010-87a3-c30de2ffd8ff.html

I connect data source through :

OLE DB (ADO) --> Microsoft Office 12.0 Access Database Engine OLE DB Provider

I input information as follows :

Data Source: C:\DataQuery\Database\MonthlyBalance.xlsx

Office Database Type: Excel

User ID: Admin

Password: <NIL>

Jet System Database: <NIL>

Jet System Password: <NIL>

I created the crystal report template, the CR template can refresh the Excel data property.

The CR data source properties is as follows :

Provider: Microsoft.ACE.OLEDB.12.0

Data Source: C:\DataQuery\Database\MonthlyBalance.xlsx

User ID: Admin

Jet System Database:

Office Database Type: Access

Use DSN Default Properties: False

Locale Identifier: 1033

OLE DB Services: -6

Extended Properties: Excel 12.0

I try the write my code by VB .NET to call the CR template and direct print to the printer (I do not need CR viewer in my requirement)

Imports CrystalDecisions.CrystalReports.Engine

Imports CrystalDecisions.ReportSource

Imports CrystalDecisions.Shared

..........

Dim cryRpt As New ReportDocument

cryRpt.Load("C:\DataQuery\MyCRTemplate.rpt")

cryRpt.PrintToPrinter(1, False, 0, 0)

The above code is running fine. The CR template can be called up and direct print to printer. But this is only the case of the CR template already saved with the data. The above code never refresh the latest data from the Excel data source.

I then add some more code as below (I also change the CR template to without saving data) :

Dim cryRpt As New ReportDocument

cryRpt.Load("C:\DataQuery\MyCRTemplate.rpt")

cryRpt.SetDatabaseLogon("Admin", "", "C:\DataQuery\Database\MonthlyBalance.xlsx", "")

cryRpt.Refresh()

cryRpt.PrintToPrinter(1, False, 0, 0)

The PrintToPrinter statement shows with error --> CrystalDecisions.CrystalReports.Engine.LogOnException: 'Database logon failed.'

I try to change the CR data source connection from C:\DataQuery\Database\MonthlyBalance.xls to UNC name \\MyServer\DataQuery\Database\MonthlyBalance.xls. Also the same error -> 'Database logon failed.'

I try all above steps by connecting to an MS Access (.accdb) data file instead of an Excel. Also modify the code as below :

cryRpt.SetDatabaseLogon("Admin", "", "C:\DataQuery\Database\MonthlyBalance.accdb", "")

I code runs with the same problem -> 'Database logon failed.'

I have the following installed :

Crystal Report 2020 full version + SP3

Visual Studio 2022

CR for VS 2022 SP33

CR for VS 2022 runtime engine for .NET framework MSI (64-bit)

AccessDatabaseEngine_X64.exe

Would be appreciated if some export could indicate me how the code SetDatabaseLogon can logon to the Excel data source and refresh the CR property.

[DonWilliams · Accepted solution]

Hi Sylvian,

You did not read that document close enough.

Firstly, Access database driver is NOT the same as the Excel driver so that's not going to work.

In CR Designer the how to doc tells you you need to the use the Office 2010 runtime and not the one you are using.

Also note you can't use the XLSX file type, it has to be saved as a XLS file:

.XLSX Format

SAP Crystal Reports will not connect directly to an .XLSX workbook. And, no default support for .XLSX format

is provided via other “out of the box” connectivity methods (ODBC, OLE DB, and so forth). Two options exist if

the Excel workbook you wish to report from is in .XLSX format:

• Open the workbook in a later version of Microsoft Excel and export the workbook to .XLS format.

• Install the Microsoft Access database engine 2010 Redistributable driver. This will add an .XLSX driver to

your computer. You may then connect using an included OLE DB driver or create a standard ODBC data

source directly from the .XLSX file.

Read more about how to on page 3.

Also read more about what CR for VS does support on the Platforms guide:

https://www.sap.com/documents/2016/06/f871031e-757c-0010-82c7-eda71af511fa.html

You are trying to connect apples to oranges, not going to work...

Also, if the data source is not the same as when the report was created you may need to use ReplaceCOnnection() API to update the reports data source. Search using Google for that API, there is a KBA on how to use it.

Don