Getting Data Out of Sage MAS90 MAS200 Using Microsoft Excel and ODBC

Getting data out of MAS90 doesn’t have to be tedious or time consuming.  With any version of Excel using the included Microsoft Query function, you can access just about any data set or combination of data sets stored in your MAS90 MAS200 Software.

To accomplish this task, you need two things, a valid and working connection to your MAS90 MAS200 Software and your user ID and Password to log in.  

When your MAS90 MAS200 software was installed on your workstation, the MAS90 ODBC "SOTAMAS90" driver was also installed.

Some data may not be accessible to every user, if you encounter a problem, please verify your access with the person charged with administering your MAS90 MAS200 Security.

With the inclusion of the ALE Lookup Engine in the later versions of the software, V3.71 and up, some of these tasks can be accomplished using that built in functionality.  But, sometimes, you may just want to see what else hanging out in your data files.

Tasks that I have used this for:
  •  Analyzing Customer and Vendor Data
  •  Troubleshooting Errors and File Corruption
  •  A snapshot of what is in the file -- helpful if you are doing imports using Visual Integrator

The list goes on.

This functionality works with both the legacy version, 3.71 and below, and the new “NORMALIZED” program structure, V4.0 and above.  You will need to have a general understanding of how the files relate to each other in order to make this little trick useful.

Customers are in one file, the invoice history behind the Customer is in another file.  Vendors are the same way.  Inventory Items are in one Table, the Extended Descriptions are in another.  For Sales Orders, you have a header file, and then you have a detail file; the header includes the Customer Name and number, the order date, terms, total dollars and the Detail file contains the line information... etc.  

You can access table information and listings of the items contained in them via the Detail View Button the MAS90 TASK Bar,  and then select the File Layouts and Program information.

Once you have decided which data table you want to work with, load Microsoft Excel.

(1.) Select the Data menu. (2.) Then select “From Other Sources”.(3.) A dropdown will appear, choose, From Microsoft Query. (4.) Select SOTAMAS90 from the list.(5.) Then click OK.

(6.) Your Mas90 Sign on Screen will appear. Choose the Company you want the data for and enter your User ID and Password, then click OK.(7.) You will now have the opportunity to scroll through the Mas90 Data Tables. Either Highlight the Table and click the ">" button to move the selection to your Qwery, or Expand the table by clicking on the "+" sign in front of the Table name.

(8.) For this example, I expanded the selection and only chose the AR Division and Customer Number. (9.) You can filter the data you want to appear in your results by utilizing the drop down filter selection criteria.  If you do not include a filter, than all data will appear. Click Next.  Note: If no data appears then you will need to check your selection. (10.) The Sort Order Screen will appear and give you the opportunity to pre sort your data.  This functionality already exsists in Excel so, as a personal preference, I do not select any sort criteria. Click Next (11.)  The Query Wizard Finsh Screen will now appear.  At this point you have the opportunity to save the query for future use, or just invoke it.  I recommend just clicking the finish button until you have the query fine tuned to your liking.  Click the Finish Button. (12.) The Excel Import Data Pop up will appear indicating which position on your worksheet the data will appear and give you the opportunity to select different views when it is imported into Excel.  You can accept the default, or make another selection. Click OK. (13.) You will again be asked to log into your MAS90 MAS200 Database.  Click OK. (14.) Congradulations!  Your data has now been transfered to Excel and is ready for you to Sort, Graph, Pivot, Print or Save.

® Sage Software the Sage Software logos, and the Sage Software product and service names mentioned herein are registered tradenames or trademarks of Sage Software, Inc. and/or its affiliated entities.  Microsoft Programs, products and service names mentioned above are registered tradenames, or trademarks of Microsoft Corporation and/or its affiliated entities. Reed Consulting Group, Inc. http://www.reedcg.com.

This article is available for download in its original PDF format here: Mas90ODBC.pdf