Creating an ODBC Data Source from an Access Database
Contents
When you installed LINGO, a Microsoft Access database for the transportation model was included as part of the installation. This file is contained in the directory SAMPLES under the name TRANDB.MDB. To register this database as an ODBC data source for our transportation model, you must start the ODBC Administrator by doing the following:
1. double-click on the My Computer icon on your desktop,
2. find the Control Panel icon and double-click on it,
3. double-click on the Administrative Tools icon, and
4. search for the Data Sources (ODBC) icon and double-click on it.
You should now see the ODBC Administrator dialog box shown below:
To install the TRANDB.MDB database as a data source, do the following:
| 1. | Click the Add button in the ODBC Administrator dialog box to reveal the dialog box below: |
| 2. | We are installing an Access data source, so select the Microsoft Access Driver option and press the Finish button. |
| 3. | In the next dialog box: |
assign the data source the name Transportation in the Data Source Name field. In the Description field, enter "Data source for a LINGO transportation model". Press the Select button and enter the name of the database "LINGO\SAMPLES\TRANDB.MDB" (this assumes LINGO has been installed in the LINGO directory—your installation may differ). The dialog box should now resemble the one shown below:
| 4. | Press the OK button and you should see the Transportation data source has been added to the list of ODBC data sources: |
| 5. | Click the OK button to close the ODBC Administrator. |
You should now be able to start LINGO and solve the TRANDB.LG4 model. LINGO knows to go to the Transportation data source for data because the model’s title (input with the TITLE statment) is Transportation. If you solve this model, you should see the following results:
Global optimal solution found at step: 6
Objective value: 161.0000
Model Title: Transportation
Variable Value Reduced Cost
CAPACITY( PLANT1) 30.00000 0.0000000
CAPACITY( PLANT2) 25.00000 0.0000000
CAPACITY( PLANT3) 21.00000 0.0000000
DEMAND( CUST1) 15.00000 0.0000000
DEMAND( CUST2) 17.00000 0.0000000
DEMAND( CUST3) 22.00000 0.0000000
DEMAND( CUST4) 12.00000 0.0000000
COST( PLANT1, CUST1) 6.000000 0.0000000
COST( PLANT1, CUST2) 2.000000 0.0000000
COST( PLANT1, CUST3) 6.000000 0.0000000
COST( PLANT1, CUST4) 7.000000 0.0000000
COST( PLANT2, CUST1) 4.000000 0.0000000
COST( PLANT2, CUST2) 9.000000 0.0000000
COST( PLANT2, CUST3) 5.000000 0.0000000
COST( PLANT2, CUST4) 3.000000 0.0000000
COST( PLANT3, CUST1) 8.000000 0.0000000
COST( PLANT3, CUST2) 8.000000 0.0000000
COST( PLANT3, CUST3) 1.000000 0.0000000
COST( PLANT3, CUST4) 5.000000 0.0000000
VOLUME( PLANT1, CUST1) 2.000000 0.0000000
VOLUME( PLANT1, CUST2) 17.00000 0.0000000
VOLUME( PLANT1, CUST3) 1.000000 0.0000000
VOLUME( PLANT1, CUST4) 0.0000000 2.000000
VOLUME( PLANT2, CUST1) 13.00000 0.0000000
VOLUME( PLANT2, CUST2) 0.0000000 9.000000
VOLUME( PLANT2, CUST3) 0.0000000 1.000000
VOLUME( PLANT2, CUST4) 12.00000 0.0000000
VOLUME( PLANT3, CUST1) 0.0000000 7.000000
VOLUME( PLANT3, CUST2) 0.0000000 11.00000
VOLUME( PLANT3, CUST3) 21.00000 0.0000000
VOLUME( PLANT3, CUST4) 0.0000000 5.000000
Row Slack or Surplus Dual Price
OBJ 161.0000 1.000000
2 0.0000000 -6.000000
3 0.0000000 -2.000000
4 0.0000000 -6.000000
5 0.0000000 -5.000000
6 10.00000 0.0000000
7 0.0000000 2.000000
8 0.0000000 5.000000
TRANDB Solution
As an interesting exercise, you may wish to redirect TRANDB.LG4 to use a second database that was provided as part of your installation. The second database is called TRANDB2.MDB and is also located in the SAMPLES directory. The main difference between the files is the dimension of the data. TRANDB.MDB involves only 3 plants and 4 customers, while TRANDB2.MDB contains 50 plants and 200 customers. If you return to the ODBC Administrator and register TRANDB2.MDB under the name "Transportation2", then you can redirect LINGO to use the new data source by changing the model title from "Transportation" to "Transportation2" in the following line in the LINGO model:
TITLE Transportation2;
The new model will have 10,000 variables as opposed to the 12 variables generated with the smaller data source. This ability to easily run different size data sets illustrates the usefulness of writing data independent, set-based models.