Community Pick: Many members of our community have endorsed this article.

Getting Data out of MAS90/200 using ODBC Connections

Published:
A frequent question here in the MAS90 Zone is, How do you get data out of MAS90 for analysis, reporting or using the data on your web pages?  

This is an introductory level tutorial to getting data out of MAS90 using ODBC. Data can be accessed by programs like Excel, Crystal Reports, SQL and VBScript among others. I will provide an example using VBScript. Everyone using Windows versions 2000 and later will have this resource available.

So, you have installed MAS90/200 and have been using it for some time. Now you need to access the data and ask “How do I get the data?” I will address that question in a moment. But first some background.

First, MAS90’s ODBC limitations. The ODBC driver as shipped from Sage is Read-Only. You can find workarounds on the Internet, although these workarounds can and do cause errors when used with MAS90 and are not recommended.

MAS90’s ODBC driver is available on every computer that can login to MAS90. If a computer can login to MAS90, the ODBC driver is already installed and working. MAS90 itself does not use ODBC for data access, but its reporting sub-system does. The standard DSN is called SOTAMAS90. It can be viewed using Windows ODBC Administrator.

MAS90’s ODBC driver enforces the standard MAS90 user login security. Login by the ODBC driver is always required. All MAS90’s standard company and module rules apply. The login requires a company code, user code and password. Your MAS90 administrator can supply you with these. Login information can be passed to the driver by a script. Scripting the login may not be approved by your system administrator, so be sure to check your company’s policies. If the login information is omitted to the driver, an interactive logon box will be presented to the user. If you pass incorrect login information from a script to the driver, an interactive logon box will be presented to the user.

How do we know what data is available from MAS90? Within MAS90, the files and their fieldnames are documented. This is sometimes referred to as the TRSG. You can view the TRSG by looking above the Module’s menu in the left pane.  There is an item called Resources. Selecting it will bring up an option in the right pane with “File Layouts and Program Information” Selecting it will display a Windows Help style module. In the left pane is a list. One of the options is “File Layouts” Double click it to get a list of modules. Double click the AR module. A listing of the tables should be expanded in the left pane. Scan down to AR_Customer. Clicking it will display a listing in the right pane. Every file is documented this way. I will use the AR_Customer file in my example below.
 
With the background information out of the way, I will present an example of extracting data from MAS90 using VBScript. I will extract data from the AR Customers file. Open Notepad or any other ASCII editor. Type in the following lines.
Set connectionToDatabase=CreateObject("ADODB.Connection")
                      connectionToDatabase.ConnectionTimeout=60
                      connectionToDatabase.Open "DSN=SOTAMAS90;" & _
                          "Company=ABC;" & _
                          "UID=rrm;"     & _
                          "PWD=wizard11"

Open in new window

These are the lines required to connect to the MAS90 ODBC driver. Notice I have told the driver which MAS90 company I want to extract data.  This is important as MAS90 may contain several companies. In this part of the example, I have included my login information. Had I omitted the last 3 lines, or entered them incorrectly, I would get an interactive login from the driver. Be aware some consider this a bad practice as the script is stored on disk in plain text and anyone may be able to view the file.

Now we need to request data from the driver. Add the following lines to the ones above.
xselect = "Select ardivisionno,customerno,customername from ar_customer"
                      Set recordCollection = connectionToDatabase.Execute(xselect)

Open in new window

I have constructed a SQL statement for the ODBC driver. Where did I get the MAS90 table name and field names? I consulted my TRSG in MAS90. I asked for the first 3 fields in my select statement. I could have asked for all fields using, for instance:
     select * from ar_customer
The second line will submit the request to the ODBC driver. If all goes well, we are ready to display some data.

Continue by adding the following lines to you code.
do while not recordCollection.eof
                          div = recordCollection("ardivisionno") 
                          id = recordCollection("customerno")
                          name = recordCollection("customername")
                      
                          wscript.echo div & "-" & id, name
                      
                          recordCollection.MoveNext
                      loop

Open in new window


These new lines will cycle through the data. I did not have to create variables to receive the field data. I could have used the fields directly in the wscript line. You can assign the data to variables, as I did here, and use VBScript’s language to transform the data further.

Ok. Two more lines and we’re done.
Set recordCollection=Nothing
                      Set connectionToDatabase=Nothing

Open in new window

These lines are for cleanup and good practice.

There you have it. Access to MAS90 data in 17 lines of code. Save your file as MASEX1.VBS. The VBS extension is very important. You can run the script in 1 of 2 ways. Use Windows Explorer to browse to where you saved the file and click on it. A message box will be displayed, one for each record. You can also run it from a command line as
    CSCRIPT MASEX1.VBS
A list will be displayed on the console.

Some additional bits. Some of you may recognize this example as ADO access. You are correct. There is more SQL syntax that could be added to the “select” statement. You can add a “where” clause and many other string, date and numeric functions. For those interested in learning more about the MAS90 ODBC driver features and syntax, download the ODBC reference on Sage’s ProvideX site at www.pvx.com. To learn more about the VBScript language and syntax, you may consult a VBScript tutorial on the Internet.
4
12,504 Views

Comments (2)

Author

Commented:
As far as I am concerned, please delete this article. I will take my business elsewhere.

Author

Commented:
Sure, lets give it a try.

rm

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.