Posts

Error: ‘Access/Excel connection is no longer supported

, ,

Description:

Error: ‘Access/Excel(DAO) connection is no longer supported…’

Report based on Excel, Access, or a Text file fail to refresh in Crystal Reports.

Could successfully refresh the report in previous version of Crystal Reports.

When refreshing a report connecting to MS Excel, or MS Access, or a Text file in Crystal Reports, it fails with the error:

 

“Failed to load database information.

“Details: Access/Excel(DAO) connection no longer supported,

use another alternative if possible, e.g. ODBC/JDBC.”

 

Followed by:  “Failed to load database information.”

 

 

Steps To Reproduce:

  1. In Crystal Reports 2016 or below, create a new report.
  2. In the Database Expert, under “Create New Connection” double click on “Access/Excel (DAO)”
  3. Select the MS Excel, or MS Access file to report from.
  4. Refresh the report, and it successfully display data.
  5. Save the report.
  6. In Crystal Reports 2020, open the same report, and refresh it.

It generates the error: “Failed to load database information. Details: Access/Excel(DAO) connection no longer supported,…”

 

Cause

  • The report connect to Excel, Access, or a Text file using Microsoft Data Access Objects (DAO), which used Microsoft JET Engine.
  • This 32bit only connectivity technology have been deprecated by Microsoft.
  • Previous versions of Crystal Reports were 32bit applications, and could still use the deprecated Microsoft DAO technology to connect to Excel, Access or a Text file, as a legacy connection type.
  • Crystal Reports 2020 is a 64bit application, and therefore it is no longer possible to connect to Excel, Access, or a Text file using DAO, since there is no 64bit version of this deprecated Microsoft Technology.

 

Resolution:

  • Use an ODBC connection to connect to one of the following data sources in Crystal Reports:
    • MS Excel
    • MS Access
    • Text file
  • For MS Excel, and MS Access, you can also use an OLEDB Connection.

 

To use an ODBC Connection:

  1. Launch the 64bit Microsoft ODBC Data Source Administrator. (C:\Windows\System32\odbcad32.exe)
  2. In the 64bit Microsoft ODBC Data Source Administrator, under the tab “System DSN”, click “Add…”
  3. Select the ODBC Driver corresponding to the type of document you want to report from:

– For MS Excel, select   : Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)

– For MS Access, select : Microsoft Access Driver (*.mdb, *.accdb)

– For a Text file, select  : Microsoft Access Text Driver (*.txt, *.csv)

 

Note

If you do not see the ODBC Drivers, then it is either because you have a 32bit version of MS Office installed, or do not have a 64bit MS Office installed. To download and install the 64bit version of the Microsoft Office ODBC drivers, go to the Microsoft website, and search for: “Microsoft Access Database Engine”, then download and install the 64bit Microsoft Access Database Engine.

  1. Then click “Finish”
  2. Add an ODBC DSN Name and select the MS Excel, MS Access, or Text file you want to report from, and click “OK”
  3. In Crystal Reports, open your report based on MS Excel, MS Access or a Text file.
  4. Under the menu “Database”, select “Set Datasource Location”
  5. In the “Replace with” section, expand “Create New Connection”, and double click on “ODBC (RDO)”
  6. Select the ODBC DSN created in the previous steps to report from MS Excel, MS Access, or aText file.

Note: For MS Excel, if the Excel sheets are not visible, then:

  1. Right click on the “Connection”, and in the contextual menu, select “Options”
  2. In the “Options” window, check the option “System Tables”, and click “OK”
  3. Back to the “Database Expert” window, right click on the connection, and select “Refresh”
  4. The MS Excel sheets will now display. Select the desired MS Excel sheet.

 

  1. Once connected, in the section “Replace with”, select the new ODBC connection, and in the section “Current Data Source”, select the current connection, and click the button “Update”
  2. Once the connection is updated, click “Close”
  3. Back to the report, save the report.

 

To use an OLEDB connection

  1. In Crystal Reports, open your report based on MS Excel or MS Access.
  2. Under the menu “Database”, select “Set Datasource Location”
  3. In the “Replace with” section, expand “Create New Connection”, and double click on “OLE DB (ADO)”
  4. Select the Microsoft Office Access Database Engine Provider.

 

Note

If you do not see the OLEDB Provider, then it is either because you have a 32bit version of MS Office installed, or do not have MS Office installed. To download and install the 64bit version of the Microsoft Office ODBC driver and OLEDB Provider, go to the Microsoft website, and search for: “Microsoft Access Database Engine”

  1. Change the Office Database type to: Excel, or Access
  2. Select the MS Excel, or MS Access file.
  3. Leave the User ID and Password blank.
  4. Once connected, in the section “Replace with”, select the new OLEDB connection, and in the section “Current Data Source”, select the current connection, and click the button “Update”
  5. Once the connection is updated, click “Close”
  6. Back to the report, save the report.

How to Gain access to Infor Reporting Tools

, ,

To gain access to Infor Reporting to create and edit reports, you’ll need to make sure your organization first has the license for the software. Consult with Infor before moving forward.

 

Example of Infor Reporting Icon:

The above screenshot shows how a user may gain access to view Infor Reporting Reports. These can be reports that show Expense Reports, User Proxies, etc.

 

To gain access to the administration tools, you’ll need to assign these two roles:

XM-AdvancedBusinessAuthor – Allows access to canned templates (example reports to build off of)

XM-ProfessionalAuthor – Allows freeform access to create new and edit existing reports.

 

These roles must be assigned in Mingle under user management tab

 

Good luck!

Lawson user is not able to add or see bookmark they have access to

, ,

If a user is having issues seeing a bookmark in Lawson, it may be because the bookmark is a parent of itself which can cause the bookmark to not appear for users even though they have access to it.

 

This issue can also create an infinite child bug in the Bookmark Manager as seen below:

In the database under the table “LOBKMARK”, we see that this BOOK_MARK 272 is the parent of itself (PARENT_BKMARK is also 272).

IMPORTANT: Always be cautious when making updates to the database.

To fix BOOK_MARK 272, we need to update its PARENT_BKMARK and set it to 0.

Here is the code to do this:

UPDATE LOBKMARK SET PARENT_BKMARK = 0 where BOOK_MARK = 272 and OBJ_ID = 285

We add the OBJ_ID in there as an extra check. It’s always recommended to build and update query with a select query first so you don’t change more than what you need.

How to preserve linked Lawson tables in Access when copying to different machines

, ,

Need an Access Lawson database to reside on the desktop of select users without having to relink all the tables each time?

If so, see the below query for the DSN-less connection with a function to relink all the tables and pass-through queries. This also just works for databases in general. Good luck!

 



Landmark Security Access

, ,

To maintain Landmark Security (classes and roles), in the Gen environment in Rich Client, go to Start > Configure > Security.  If you don’t see the Security or Configure, you will need to have your Security Administrator give you access to it.  The Infor delivered role for this is “ConfigConsoleSecurityadmin_ST”.  Have that role added to your account and wait about 30 minutes for the sync to complete.

Best Practice to Load File into IPA

, ,

The Data Iterator node is commonly used to loop through records but it can also read a file into IPA. (For more on the Data Iterator Node, visit: https://www.nogalis.com/2017/05/04/ip-designer-series-the-data-iterator-node/)

Based on the responses of seasoned IPA developers on the Infor/Lawson forums, the best way to ‘load’ in a file to an IPA flow is to use a FileAccess node followed by a DataIterator node. This speeds up the flow considerably as the FileAccess node would read the file into memory and then the DataIterator node can use the data from the memory instead of reading and closing the file multiple times.

First ‘load’ or ‘read’ the file into IPA using the FileAccess node. Then set DataIterator to process the Data (and not File) and set the source to be the FileAccess_outputData. This should noticeably improve the performance of the flow as the data is just being loaded into memory once by the FileAccess.