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.

Refresh user list in LID

, ,

If you have added environment users in Lawson, and you are not seeing the new users in listusermap, there’s an easy fix for that.

You will need to run it with the command -a.  This will refresh the cache and show all users in NT order.

listusermap -a

 

If you would like to run other commands, here are some other tips for the listusermap usage:

-a – list all users,

-c – list from cache (Default),

-n – clean up cache,

-u – find user,

-p – find account,

-h – find historical user (NTxxxxxxxx),

-o – show historical user names in list,

-k – list archived user cache

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 Portal 404 Error

, ,

If you receive the error message in Lawson Portal “The Server has not found anything matching the requested URI”, check the WebSphere SystemOut.log for the message “Failed to load webapp: context root /* is already bound.”  This means that one of your WAS applications (most likely the default app) is configured to start before the Lawson IOS application.  To fix this, either delete the Default Application, or configure its startup behavior.  Go to WAS Console > Applications > Enterprise Applications.  Click on the Default Application then “Startup behavior.”  Change the startup order to some larger number so the Default Application will attempt to start last in the order.  Save the changes and synchronize the nodes.  Then, start your IOS application.

 

 

 

Windows: Cleaning up LAWDIR/productline/work

, ,

If you have several files in the LAWDIR/productline/work directory that are taking up a lot of space and need to clean them up, most can be deleted, but be aware that the files with UPPERCASE file names are often used to transfer data to non-Lawson systems or ACH bank files, and they may be waiting to be used by a future process that has not run yet.

 

The following procedure is to clean up print files, work files, RQC files, user home directory files, and WebSphere trace and dump files by either running a program or by defining a recurring job that calls the program.

 

Automated Cleanup of Print Files, Work Files, and Other Files

Use this procedure to clean up print files, work files, RQC files, user home directory files, and WebSphere trace and dump files by either running a program or by defining a recurring job that calls the program. Before running the program or the recurring job, you must set up configuration files. These files enable you to set the cleanup options, exclude specific files from the cleanup process (by file name or by the user name associated with the files), and to specify date ranges for the files to be deleted.

The types of files to be deleted include:

  • Completed job entries
  • Completed jobs forms and the associated job logs
  • Batch report files
  • All print files from the print manager
  • Files from %LAWDIR%\productline\work directory
  • All user-related files from the %LAWDIR%\RQC_work, %LAWDIR%\RQC_work\log, %LAWDIR%\RQC_work\cookies directories
  • Log files from the user’s home directory: %LAUNTDIR% directory
  • WebSphere trace and dump (.dmp, .phd, javacore and .trc) files that are in subdirectories of the <WASHOME>\profiles directory.

To clean up print files, work files, RQC files, and other files:

  1. Configure the prtwrkcleanup.cfg file.

You can edit the parameters in the prtwrkcleanup.cfg file in two ways:

  • By using the Lawson for Infor Ming.le Administration Tools. See Configuring Automated File Cleanupin the Lawson for Infor Ming.le Administration Guide, 10.1. (This option is only available in Lawson for Infor Ming.le 10.1.)
  • By directly editing the file in the %LAWDIR%\system directory. See Directly Updating the prtwrkcleanup.cfg File.
  1. Configure the prtwrkcln_usrnames.cfg file.

This configuration file is divided into multiple sections:

  • Section 1: usernames list for print and completed job cleanup
  • Section 2: usernames list for RQC cleanup
  • Section 3: usernames list for users home directory cleanup.

The script uses each different section for a different cleanup job. Make sure to put usernames in the right sections to avoid undesired outcomes.

You can enter multiple usernames in either a comma-separated format or a line-break-separated format.

For example:

Username1,Username2,Username3…

Or

Username1

Username2

Username3

Note: Do not remove the section dividers.

  1. Configure the prtwrkcln_exclude.cfg file.

Use this file to specify a list of file names to be excluded from the work file cleanup process.

You can enter multiple file names in either a comma-separated format or a line-break-separated format.

For example:

Filename1,Filename2,Filename3…

Or

Filename1

Filename2

Filename3

  1. If you want to run the cleanup program just once, open a command line session and follow the substeps below. (Note that a recurring job may be more useful in the long term. See the next main step below.)
    1. Ensure that the prtwrkcln executable exists in %GENDIR%\bin.
    2. In a command line session, navigate to %GENDIR%\bin.
    3. At the command line, enter the following command:

prtwrkcln.exe

  1. If you want to run the cleanup program via a recurring job, use the following substeps.
    1. In Lawson for Infor Ming.le, navigate to Bookmarks> Jobs and Reports > Multi-step Job Definition.
    2. Specify this information to define a multi-step job.

Job Name

Specify a name for the multi-step job.

Job Description

Specify a description for the multi-step job.

User Name

Displays the name of the user defining the job.

Form

Specify prtwrkcln. (This assumes this form ID exists. Use the For ID Definition utility (tokendef) to check if it exists and to add it as an environment form ID if necessary.)

Step Description

Specify a description for the step.

  1. Click Addto save the new job.
  2. Navigate to Related Forms> Recurring Job Definition. Define a recurring job according to the instructions in the “Recurring Jobs” topic in Infor Lawson Administration: Jobs and Reports.

UNIX: Cleaning up LAWDIR/productline/work

, ,

If you have several files in the LAWDIR/productline/work directory that are taking up a lot of space and need to clean them up, most can be deleted, but be aware that the files with UPPERCASE file names are often used to transfer data to non-Lawson systems or ACH bank files, and they may be waiting to be used by a future process that has not run yet.

 

The following procedure is to clean up print files, work files, RQC files, user home directory files, and WebSphere trace and dump files by either running a program or by defining a recurring job that calls the program.

 

Automated Cleanup of Print Files, Work Files, and Other Files

Use this procedure to clean up print files, work files, RQC files, user home directory files, and WebSphere trace and dump files by either running a program or by defining a recurring job that calls the program. Before running the program or the recurring job, you must set up configuration files. These files enable you to set the cleanup options, exclude specific files from the cleanup process (by file name or by the user name associated with the files), and to specify date ranges for the files to be deleted.

The types of files to be deleted include:

  • Completed job entries
  • Completed jobs forms and the associated job logs
  • Batch report files
  • All print files from the print manager
  • Files from $LAWDIR/productline/work directory
  • All user-related files from the $LAWDIR/RQC_work, $LAWDIR/RQC_work/log, $LAWDIR/RQC_work/cookies directories
  • WebSphere trace and dump (.dmp, .phd, javacore and .trc) files that are in subdirectories of the <WASHOME>/profiles directory.

To clean up print files, work files, RQC files, and other files:

  1. Configure the prtwrkcleanup.cfg file.

You can edit the parameters in the prtwrkcleanup.cfg file in two ways:

    • By using the Lawson for Infor Ming.le Administration Tools. See Configuring Automated File Cleanupin the Lawson for Infor Ming.le Administration Guide, 10.1. (This option is only available in Lawson for Infor Ming.le 10.1.)
    • By directly editing the file in the $LAWDIR/system directory. See Directly Updating the prtwrkcleanup.cfg File.
  1. Configure the prtwrkcln_usrnames.cfg file.

This configuration file is divided into multiple sections:

    • Section 1: usernames list for print and completed job cleanup
    • Section 2: usernames list for RQC cleanup
    • Section 3: usernames list for users home directory cleanup.

The script uses each different section for a different cleanup job. Make sure to put usernames in the right sections to avoid undesired outcomes.

You can enter multiple usernames in either a comma-separated format or a line-break-separated format.

For example:

Username1,Username2,Username3…

Or

Username1

Username2

Username3

Note: Do not remove the section dividers.

  1. Configure the prtwrkcln_exclude.cfg file.

Use this file to specify a list of file names to be excluded from the work file cleanup process.

You can enter multiple file names in either a comma-separated format or a line-break-separated format.

For example:

Filename1,Filename2,Filename3…

Or

Filename1

Filename2

Filename3

  1. If you want to run the cleanup program just once, open a command line session and follow the substeps below. (Note that a recurring job may be more useful in the long term. See the next main step below.)
    • Ensure that the prtwrkcln executable exists in $GENDIR/bin.
      • In a command line session, navigate to $GENDIR/bin.
      • At the command line, enter the following command:

prtwrkcln.

  1. If you want to run the cleanup program via a recurring job, use the following substeps.
    • In Lawson for Infor Ming.le, navigate to Bookmarks> Jobs and Reports > Multi-step Job Definition.
      • Specify this information to define a multi-step job.

Job Name

Specify a name for the multi-step job.

Job Description

Specify a description for the multi-step job.

User Name

Displays the name of the user defining the job.

Form

Specify prtwrkcln. (This assumes this form ID exists. Use the For ID Definition utility (tokendef) to check if it exists and to add it as an environment form ID if necessary.)

Step Description

Specify a description for the step.

  • Click Addto save the new job.
    • Navigate to Related Forms> Recurring Job Definition. Define a recurring job according to the instructions in the “Recurring Jobs” topic in Infor Lawson Administration: Jobs and Reports.

Clearing the Internal WebSphere Cache

, ,

Problem:

How do I delete or clear out the IBM WebSphere Application Server (WAS) temporary directories and cached files?

Summary:

This guide explains the process of removing or erasing the temporary directories and cached files in IBM WebSphere Application Server (WAS). It includes the appropriate situations for performing this task, the intended users, and the step-by-step instructions.

When is it necessary to perform this action?

If you encounter issues where the deployment manager, nodeagent, or application server fails to start, you can attempt the following steps. However, unless specifically requested by a support analyst, there is no need to carry out these actions.

 

Who should carry out this task?

System Administrators are responsible for executing these steps.

 

How is this done?

Follow the instructions below for each profile located within WAS_HOME/profiles (including Dmgr01 and AppSrv01, or whichever name your application server profile has).

 

  1. Stop the Deployment Manager, nodeagent, and application servers.
  2. Create backups of the existing configurations:
  3. cd PROFILE_ROOT/bin
  4. Run backupConfig
    1. Unix: ./backupConfig.sh backup_file
    2. Windows: backupConfig backup_file
    3. IBM i: ./backupConfig backup_file
  5. Repeat for every profile you have (Dmgr, AppSrv01, etc.)
  6. Rename the contents of the following directories or rename these temp directories. They will be recreated when you restart the servers.
  7. PROFILE_ROOT/wstemp
  8. PROFILE_ROOT/temp
  9. PROFILE_ROOT/config/temp (*** DO NOT REMOVE THE ENTIRE CONFIG DIRECTORY, JUST TEMP ***)
  10. Repeat for every profile you have (Dmgr, AppSrv01, etc.)
  11. Delete the javasharedresources directory:
  12. Unix and IBM i: /tmp/javasharedresources
  13. Windows:
    1. C:\Windows\System32\config\systemprofile\AppData\Local\javasharedresources
  14. From a command prompt or Qshell prompt, run the following command to initialize the OSGI configuration and clear the OSGI class cache:
  15. cd PROFILE_ROOT/bin
  16. Unix:
    1. ./osgiCfgInit.sh
    2. ./clearClassCache.sh
  17. IBM i:
    1. ./osgiCfgInit
    2. ./clearClassCache
  18. Windows:
    1. osgiCfgInit
    2. clearClassCache
  19. Repeat step 5 for the Dmgr01 profile and any other profiles present on your system.
  20. Start the Deployment Manager, nodeagent, and application servers.

 

Good luck!

ServerExpress errors: Restart the Server Express License Manager and License Manager is corrupt

, ,

Description:

To resolve the error message, “Restart the Server Express License Manager or License Manager is corrupt.” The errors are listed as compile error messages 191, 192, and 197, follow the troubleshooting steps outlined below.

 

Enter the command ps -ef|grep mfl to see if your License Manager is running. If the License Manager isn’t running, start it. If the License Manager is running, kill and re-start it by moving to the mflmf directory and entering the command sh ./mflmman.

 

If the license database is corrupt, go to the License Manager directory. (Note: The License Manager directory is the location where the license was installed.) Remove the following four files from the mflmf directory: mflmfdb, mflmfdb.idx, mflmfdbX, and mflmfdbX.idx. After these files have been removed, run License Administration Services (mflmadm) and re-install the licenses.

 

Follow these steps if you want to add or re-add developer licenses:

Use the cd command to move to the directory where License Manager was installed.

Execute the mflmadm program by entering the command ./mflmadm.

Press F3 (Install) to install the ServerExpress and/or the MicroFocus Cobol license.

When prompted, enter your key and serial number. ( Note: You must hit the slash ( / ) key twice.) Press Enter to save your key and serial number.

Press F3 (Install) to install and F7 (Refresh) to refresh. Press F5 (Browse) to see your ServerExpress license. Press F6 (More) to see both your ServerExpress and MicroFocus Cobol licenses.

Start the License Manager by going to the mflmf directory and entering the command sh ./mflmman. To verify that the License Manager is running, enter the command ps -ef|grep mfl. (If the License Manager is running, a root mflm_manager process should be returned.)

 

If the License Manager is still corrupt, remove the entire mflmf directory and use the cd command to move into the $COBDIR/lmf directory. Run lmfinstall. Select just the ServerExpress install option. You can either enter your developer serial number and license during this ServerExpress install OR you can enter them after the install has completed.

 

 

Follow these steps if you want to enter your developer serial number and license after your ServerExpress install is complete:

Use the cd command to move to the mflmf directory.

Run ./mflmadm.

Press F3 (Install) to install, and add your serial and license number.

Press F3 (Install) again.

Press F7 (Refresh) to refresh.

Verify that the License Manager is running by entering the command ps -ef|grep mfl. If the License Manager is running, a root mflm_manager process should be returned. If the License Manager isn’t running, move to the mflmf directory and run the command sh ./mflmman to start your License Manager.