How to improve TaskCentre query performance in Sage 100/MAS 90

Overview

By default, TaskCentre connects to Sage ERP 100 (formerly MAS 90/200) via its ProvideX ODBC Driver. This imposes limitations on both performance and in the SQL syntax that is generated when building a TaskCentre Query. You can get around these limitations by passing through an Microsoft Access database (MDB).

This Access DB uses Linked Tables to expose the data to TaskCentre and connects to ProvideX using a variation of the standard user DSN "SOTAMAS90" that instead uses silent logon. The advantage of this approach is that TaskCentre just sees the MDB and can use standard query syntax; plus this method also handles dates far easier than vanilla ProvideX ODBC. The problem then, is how to create this MDB with the linked tables.

But we have an app for that (see below). Our utility that was created by ISM, a Sage 100 ERP Reseller, creates the MDB dynamically and so ensures that it's easy to build and that any and all custom fields and tables are presented and made available to TaskCentre. So here are the step-by-step instructions on how to set this up.

Create the Data Source

First thing to do is create the System DSN. It is important that this is a System DSN rather than a User DSN since TaskCentre will not see the User DSN when running the task, even if it does see it when building and editing the task. So select System DSN from the ODBC Data Source Administrator and click Add.

NB: If you are working on a 64-bit Windows System, the DSN has to be 32-bit else TaskCentre won't see it, so use c:\Windows\SysWOW64\odbcad32.exe instead of "Data Sources" under Admin Tools (in Control Panel).

ODBC Data Source Administrator

Click Add and name the DSN. Enter a description and Database Directory. This is usually:

C:\Program Files\Sage Software\ERP Server\MAS90

though this may vary according to installation options and whether MAS 200 is being used.

ODBC Driver - Basic

Leave the Server Tab blank.

ODBC Driver - Server

For a silent connection, enter the Company Code, Default User ID and password.

ODBC Driver - Logon

Enter options as shown. The Prefix for Data Files is:

C:\Program Files\Sage Software\ERP Server\MAS90\SY\, C:\Program Files\Sage Software\ERP Server\MAS90\==\.

The Path to Views DLL is:

C:\Program Files\Sage Software\ERP Server\MAS90\HOME

ODBC Driver - Options

Finally, verify the connection string if needed and test the Connection.

ODBC Driver - Debug

Create the Linked Tables MDB

Next, obtain the TaskCentre MDB Linker via the link below.

Extract it to an empty folder and run the executable. Select the name and location of the MDB file and Select the ODBC Connection created above.

TaskCentre MDB Linker

Click Connect. Since you have set up for silent connection you should not get prompted for a MAS Company, User ID or password.

TaskCentre MDB Linker

The linker will display all the ProvideX tables allowing you to select individually, select all or by a Group. For a Group just enter the first couple of letters, e.g. AP, AR etc. Repeat until you have all the tables you need selected. Click Create and the MDB will be built. A log file is created in the same folder should there be a problem.

The MDB is now ready to use in TaskCentre

Make the TaskCentre Connection

In Tools>Input select Database Query (ODBC) and add a new connection, selecting the Microsoft Access Driver.

Select TaskCentre Data Source

Click OK and navigate to the location of the MDB created above. Click OK.

Select MDB

Name the connection and click OK.

Now go back in to the new connection and select the SQL Tab. Under "Show Table Types" add "Synonym" and click OK to save. If you miss this step, the tables won't show in the ODBC Query Tool.

Set Table Types

The connection is now ready for use.

Use the Query

Note that you will only see the tables (Groups) based on the ones selected when building the MDB file.

If you need to work with dates, unlike ProvideX you can use standard date notation. The Query Syntax is compatible with Access SQL.

And Finally ...

My thanks to ISM for providing the TaskCentre MDB Linker Tool to the Sage MAS 90/200 Community for no charge.

Download the MDB Linker

Please note: The user is granted a non-exclusive license to use the software free of charge which is provided as is with no warranty expressed or implied. It is the user's responsibility to establish its fitness for purpose and to thouroughly test in his/her own environment. Fisher Technology LLC and ISM can or will accept any liability whatsoever, consequentual or otherwise, for its use.

comments
0