How to create a silent connection to an ODBC data source

Summary

How to create a silent connection to an ODBC data source in Sage 100 How to connect to the Sage 100 ODBC data source without logging into the database each time

Resolution

CAUTION: This solution requires advanced knowledge of your computer’s operating system. Contact your system administrator for assistance. Modifying your Windows Registry incorrectly can severely affect system operations. Sage is not responsible for operation issues caused by incorrectly modifying your Windows Registry. Always create a backup of your data before proceeding with advanced solutions.

Note: This procedure may or may not work on any given operating system. Sage 100 Customer Support is not responsible for assisting or troubleshooting with this procedure, nor with setting up an external data connection for a third-party product to access Sage 100 data. Assistance by a qualified technician or consultant may be necessary.
Note: Steps are included in the Online Manual for Crystal Reports in Appendix C, under “Creating a Silent Connect DSN”.

A new Data Source must be created. The existing SOTAMAS90 is reserved for Sage 100 ERP use and cannot be used to create a Silent Connection. Using SOTAMAS90 will result in the silent ODBC connection failing once Sage 100 ERP itself is started from the workstation. The user will be prompted for the Company code, User Code and Password the next time SOTAMAS90 is accessed from the external source.

  1. From the Windows Desktop, open Start, Control Panel, Administrative Tools, Data Sources.
    • Note: For 64-bit operating system, you must run ODBCAD32.EXE from the “C:\Windows\SysWOW64” folder to access the 32-bit ODBC Data Source Administrator.
  2. On the User DSN tab, click the Add button to open the Create New Data Source window.
  3. Select MAS 90 4.0 ODBC Driver and click the Finish . The ProvideX ODBC Driver Setup dialog box opens
  4. Enter a new data source name (DSN). (e.g. SOTAMAS90_silent). Do not use spaces or blanks in the data source name.
  5. Enter a description for the data source.
  6. Enter the database directory location; this is the path to the ..\MAS90\ folder (e.g. X:\Sage\Mas90).
    Note: If the path is unknown, use the data source path from the SOTAMAS90 DSN.
  7. Leave the ‘Definition File’ field blank.
  8. Select Logon tab enter a valid 3-digit Sage 100 ERP Company Code.
    Note: Enter this in UPPER case letters. Entering the company code in lower case may cause the silent ODBC connection to fail.
  9. Enter the default User ID (Sage 100 ERP User Code).
    Note: Enter this in UPPER case letters.
    Note: Depending on the Sage 100 ERP version, the User Code may have to be three-digits.
  10. Enter the Sage 100 ERP Password (if necessary).
    Note: Do not use spaces or blanks in the password name.. If the password contains spaces, it must be changed in User Maintenance.
  11. Click OK.
  12. Exit the ODBC Data Source Administrator and Control Panel
  13. Use the external application to connect to the silent connect data source. The data should be accessed without requiring a login.

Note: In some applications, User DSNs, such as SOTAMAS90_Silent will be displayed as a “Machine Data Source” since a User DSN is a machine specific concept.

Note: Be sure to disable (uncheck) the ‘Dirty Read’ and ‘Burst Mode’ checkboxes. An explanation of both options is below:

Dirty Read:
Default value = Enabled

If enabled, the driver attempts to use data already within its buffers from prior reads. In theory, an update could occur while the query is in progress and may be missed. In practice this is not likely to happen.

Burst mode:
Default value = Enabled

Note: Burst mode can be used when the execution of an SQL command and its associated ‘data fetches’ is going to be done without the application delaying for user input. The ODBC driver will apply short-term locks to improve performance between data fetch commands. When the application executes a SQL command then fetches a row, the physical data file will remain locked until at least 20 rows have been fetched (or EOF). This can be used with Report style access but should not be used for interactive access.