Connecting to an External ODBC database e.g. Sage 100

4 minute read time.

I needed to establish a connection to the Sage 100 database to allow me to check details about a customer record within the integrated system. A direct connection to an ODBC database is a very useful way to allow realtime queries to be run against the ERP data from within the Sage CRM interface.

Full integration that synchronizes data updates with an external ERP package is not a trivial task. Some of the things that a developer has to consider are discussed in the article "Integration with External Systems and the Synchronization of Application Data".

Although I am working with the Sage 100cloud I am going to keep things as simple as possible. I only need to open the connection to view data and I am not proposing to by pass business logic; I would not use the ODBC connection for data manipulation only for reading.

Note: The Example Driver I am using (MAS 90 4.0 ODBC Driver) is READ ONLY. Any attempt to use the DSN to update data will result in an error.

Sage CRM can connect to wide variety of database types. I have discussed this previously in the article "Linking to 3rd Party Databases".

Sometimes there can be challenges to linking to external databases because of quirks such as the Database being case sensitive. See the article "Database Case-Sensitivity and Connecting to an External Case Sensitive Database" for a discussion of what to do.

A Data Source Name (DSN) is required in the setup of ODBC in order to specify the connection information for a database server. Sage CRM uses the information contained within the DSN to locate and log on to a database.

To view the available ODBC DSN on the Sage CRM server you would need to open the windows control panel folder and then view administrative tools. The short cut to manage Data sources can be accessed.

The version of Sage 100cloud I was using provided a User DSN called SOTAMAS90.

User DSNs work only for a specific user on a machine. Therefore, if someone other than the person who created the user DSN logs onto that machine, then the User DSN will not show up. This DSN is stored in the Current User section of the Registry.

I needed to allow Sage CRM to use the ODBC connection, so I had to create a System DSN. A System DSN works for anyone using that system. For example, no matter who logs onto the machine, system DSNs can be seen by all users, and they are stored in the Local Machine section of the Registry.

To create the System DSN I had to do the following:

I clicked on the System DSN tab and then click the "Add..." button. This opened a dialog box that asked me to select the driver to be used for the new Data Source. I selected the "MAS 90 4.0 ODBC Driver" and then clicked finished.

I had to provide the basic information. The Data Source has to be given a name, a description and I need to specify the path to where my providex.ddf file had been created. I called my DSN 'myMAS90'.

I then provided the Logon information that I was using. I defined the Company Database within Sage ERP MAS I was going to connect to, the User name and password to access the system. I made sure that the Company code (e.g. ABC) was the correct case.

I then made sure the information within the options tab was correct.

  • Prefix for data files: C:\Program Files\Sage Software\ERP Server\MAS90\SY\, C:\Program Files\Sage Software\ERP Server\MAS90\==\
  • Paths to View DLL: C:\Program Files\Sage Software\ERP Server\MAS90\Home\

The remaining information I left as default, but please refer to the Sage ERP MAS 90 documentation for more information regarding these.

Finally to be sure that my settings were correct I tested them using the "Test Connection" button on Debug tab.

Note: I found the information provided by the Connection string useful when setting up a linked server. But that is another article.

Creating the Connection in Sage CRM

Once I had the System DSN connection created it was very simple to establish the connection.

I logged on to Sage CRM as the System Administrator and navigated to

Administration -> Advanced Customization -> Tables and Databases

I then clicked the 'New Database Connection' button.

The Database name (and description) is the name of the System DSN created earlier. I provided a valid User Name and Password for the ERP connection.

Once the connection definition has been saved and the connection to the database made. I could then add a link to a table.

I clicked the 'New Table Connection' button and defined a connection to the ar_customer table.

This table is now able to be further described using Sage CRM meta data.

The ar_customer table can now be referenced in code within the screens and table scripts e.g.

[code language="javascript"]
var myRecord = CRM.FindRecord("ar_customer","customername like 'Bres%'");
[/code]

List blocks and screens can now be defined that can be referenced in ASP pages and in .NET assemblies.

Note: The odbc driver demonstrates case sensitivity for data but not for table names. So for example

  • select * from ar_customer where customername like 'bres%'

would not find any data but

  • select * from ar_customer where customername like 'Bres%'

would.

and

  • select * from ar_customer where CUSTOMERNAME like 'Bres%'

works just like the previous example.

Parents Comment Children
No Data