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