How to connect to Sage 100

Question

I am developing a Windows Forms application which is required to connect to Sage 100 and get order information. I am using ASP.NET and SQL Server 2008.

Is there a way to connect to the sage 100 database?

Answer 1

If you've installed the ODBC driver that came with Sage 100, you can get a provider string by adding a System DSN in your ODBC Data Source Administrator (Start Menu -> Administrative Tools -> Data Sources (ODBC)).

Click the System DSN tab, then Add... On the Basic tab, Give the DS a name and description, then enter the database directory. Just as the dialog box says, it should be the directory that contains your PROVIDEX.DDF file in your Sage 100 installation.

On the Logon tab, enter the company code, user ID and password for the company you want to access.

On the Options tab, enter the following in the Prefix for data files box:

<Sage 100 ERP directory>\SY\, <Sage 100 ERP directory>\==\

Enter the following in the Path to Views DLL box:

<Sage 100 ERP directory>\Home

Obviously on both of those, you want to use your actual Sage 100 ERP directory.

Check Dirty Read, Burst mode and Strip trailing spaces.

Then, on the Debug tab, test your connection. If it works, click Connection String and copy the connection string.

Next, in SSMS, add a new Linked Server. Enter a Linked server name, choose Microsoft OLE DB Provider for ODBC Drivers from the dropdown list, enter a Product name (doesn't matter what you put there) and paste the connection string in the Provider String box. Hit OK and the new Linked Server should appear. Now, you can create Views in your app database to access the Linked Server and use those views just like data tables.