Crystal Report template not refresh the data from Excel source (using VB code)

[sylvianbca]

Hello,

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

I create my CR report 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>

The CR report template is tested and running fine. The CR report template can extract data from Excel correctly.

I then write my vb code to automate the prcoess(To load the CR template and then export result to PDF format. I do not need to use the CR smart viewer).

When I check the output PDF file. It is strange that the CR only load the saved data(saved in the CR template) but not refresh the actual data from the Excel source.

I try the add the code .DiscardSavedData as below :

.DiscardSavedData

.....

.ReadRecords

When the code run to .ReadRecords. It shows error "Login failed. Details: [Database Vendor Code -69141536]".

(If I remove the line .DiscardSavedData, it can pass through .ReadRecords. Then the output PDF will only load the saved data in CR but not getting the actual data from Excel)

I then add the code .LogOnServer as below :

.DiscardSavedData

.LogOnServer "crdb_ado.dll", "C:\DataQuery\Database\MonthlyBalance.xlsx", "", "Admin", ""

.ReadRecords

When the code run to .LogOnServer. It shows error "Run-time error '-2147189176 (80047e48). Login failed."

I tried all combination as below. Also show ""Run-time error '-2147189176 (80047e48). Login failed."

.LogOnServer "crdb_ado.dll", "C:\DataQuery\Database\MonthlyBalance.xlsx", "", "Admin", ""

.LogOnServer "crdb_ado.dll", "C:\DataQuery\Database\MonthlyBalance.xlsx", "MonthlyBalance.xlsx", "Admin", ""

.LogOnServer "crdb_ado.dll", "C:\DataQuery\Database\MonthlyBalance.xlsx", "", "", ""

.LogOnServer "crdb_ado.dll", "C:\DataQuery\Database\MonthlyBalance.xlsx", "MonthlyBalance.xlsx", "", ""

......

I checked some document saying that the LogOnServer function may need you to provide a password.(means password should not be blank)

I then try again to connect CR data source with password provide:

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

I input information and re-assign password as follows :

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

Office Database Type: Excel

User ID: Admin

Password: Admin

It shows error message :

Logon failed.

Details ADO Error Code: 0x80040e4d

Source: Microsoft Access Database Engine

.....

SQL State: 3028

Native Error: -124520301 [Database Vendor Code: -124520301]

It seems I cannot assign new password under the OLE DB (ADO) datasource setup.

Would be appreciate if someone could assist for the case.

Thanks.

[DonWilliams · Accepted solution]

Same as this post:

https://answers.sap.com/questions/13825758/cr-data-source-connection-to-the-excel-by-vb-net.html

Please don't post the same question multiple times

[DellSC]

In addition to what Don said, I highly recommend turning OFF "Save data with report". When it's turned on the size of the .rpt file is larger than when it's off and you have to discard the data. If it's off, you don't have to go through the steps of discarding the data, so it makes you code simpler. In over 25 years of working with Crystal, I have never had a circumstance where it made sense to save data with the report in a production environment. The only use I see for it is here in the forum when I want to help someone diagnose an issue with a report.

-Dell

[DonWilliams]

Hi Sylvian,

What CR for VS SP are you using?

Get SP 33 from here:

https://wiki.scn.sap.com/wiki/display/BOBJ/Crystal+Reports%2C+Developer+for+Visual+Studio+Downloads

Note there is an installer for VS 2010->2019 and a new one for VS 2022.

Error typically means CR runtime can't find the database client.

CR 2020 is 64 bit so it's using Windows 64 bit Client.

In your Project be sure to set it to x64 and not AnyCPU, CR runtime is for your project platform and not the OS.

Don

[sylvianbca]

Hi Don,

The project code works after changing to x64 in VS. Thanks for your advice.

Sylvian