How to import data into Microsoft Access or Excel from Sage 100

Description

How to import data into Microsoft Access or Excel from Sage 100
How to export Sage 100 data into Excel

Resolution

Note:

  • The following instructions were written for Microsoft Office 2010 programs, and may vary for different versions. Please see that product’s Help for instructions, or contact their technical support department. Sage is not responsible for assisting with the use of non-Sage products.

  • Microsoft Excel or Access must have ODBC functionality in order to access Sage 100 ERP data.

Microsoft Access:

  1. Select File, Get External Data, Link Tables (or ‘Import’).
    Note: If ‘Link Tables’ is selected, the data can be viewed but not changed. ‘Import’ will create a new table within Access that can be edited.

  2. Select ODBC Databases() from the ‘File of Types’ drop-down box (Usually the last item listed).

  3. The Select Datasources window should appear and default to Data Sources in the ‘Look in’ field.
    Note: If ‘ODBC Databases()’ is not listed, reinstall MS Access with ODBC Functionality selected.

  4. Select SOTAMAS90.DSN and click OK

  5. The SOTAMAS90 ‘User Login’ dialog box should display, log on to the Sage 100 ERP database

  6. Select the desired table and click OK. The table will appear in the Tables section of MS Access.

Microsoft Excel:

Notes:

  • For 64-bit versions of Microsoft Office, the ODBC Add-In must be installed. Within Excel, select Tools / Add-Ins and select the ‘ODBC Add-in’ check box (if necessary), then click ‘Ok’.

  • The steps differ if ‘Use the Query Wizard to Create/Edit Queries’ check box is selected when SOTAMAS90 is selected.

Steps:

  1. Within Excel, select Data tab, select From Other Sources, From Microsoft Query. The ‘Choose Database’ window should appear.

  2. On the Databases tab, select SOTAMAS90 from the database list and click Ok
    Note: To use the wizard, select the ‘Use the Query Wizard to Create/Edit Queries’ check box.

  3. Log on with your Sage 100 ERP credentials to the database when prompted.

  4. The list of Sage 100 ERP tables should display.

  5. Select the desired table(s) or click + sign to expand the table name to show fields within the table.

    • Note: If two tables are linked with an equal (=) join, both tables must have the field value linked, or a record will not export.

      • Example: If an equal join link is used for the ExtendedDescriptionKey in both CI_Item and CI_ExtendedDescription, then records without an Extended Description key will NOT show up in the results.

  6. Click > button to add to worksheet or double-click each desired field to add it to the worksheet. The field list for each selected table will display on the right side in the Columns in your query. Click Next after all fields are selected

  7. Select applicable filter criteria if applicable

  8. Select applicable sort criteria if applicable

  9. Click Finish to Return Data to Microsoft Excel

  10. Select OK to Import Data

  11. Select applicable Company Code and log on with your Sage 100 ERP credentials to the database

Support

If necessary, seek the assistance of Acute Data Systems to help provide assistance with this process.