[Help-glpk] How to use Access mdb in Windows with ODBC in MathProg (GMPL)

[Noli Sicad]

Hi,

I am posting "How to use Access mdb in Windows with ODBC in MathProg
(GMPL)" as part of how to .. in MathProg / GLPK. Probably this will
encourage glpk/mathprog users as well to post "How to .. in MathProg /
GLPK.

Anyway, here is my "How to use Access mdb in Windows with ODBC in
MathProg (GMPL)"

Transp_odbc_mdb.mod as an example

Setup ODBC for Access mdb in Windows XP (e.g. transp.mdb)

1. Click Start -> Settings -> Control Panel -> Adminstrative Tools (2 clicks)
2. Data Sources (ODBC) (2x clicks) -> Select MS Access Database then click Add
3. Select MS Access Database driver (*.mdb) and click Finish
3. Type 'glpk' in Data Source Name, Description: "Access Mdb for
MathProg" then OK
4.  Click 'Configure', then Click 'Advanced'
5. Login Name: glpk
6. Password: gnu  then Click OK
7. Click Select.. in Database, then locate the your Access, mdb (e.g.
transp.mdb). Better put it in your model folder (e.g. .../sql)

Running the transp_odbc_mdb.mod using the transp.mdb
8. Download transp_odbc_mdb.rar in this email, unzip it and put it in
glpk4.42/examples/sql folder
9. Run transport_odbc_mdb.mod in GUSEK and see your results of the model

Tip / note: In creating tables in Access, no 2 primary keys are
allowed.  The solution this problem is just ignored if tables require
2 primary keys.  Don't put any key in that table as in the case of the
transp.sql script presented by Xypron and Andrew (I think).

See the result of the run below.

I hope this would put a nail to this Access (*.mdb) database question
in MathProg :-).

Next,  How to use sqlite and spatialite in Windows with ODBC in MathProg (GMPL)

Andrew, would it be possible to include this example in the glpk/gmlp
in the next release?

Noli

~~~~~~~~~~~
>C:\gusek_0-2-9\gusek\glpsol.exe --cover --clique --gomory --mir -m 
>"transp_odbc_mdb.mod"
GLPSOL: GLPK LP/MIP Solver, v4.42
Parameter(s) specified in the command line:
 --cover --clique --gomory --mir -m transp_odbc_mdb.mod
Reading model section from transp_odbc_mdb.mod...
Reading data section from transp_odbc_mdb.mod...
95 lines were read
Reading plants...
Connected to ACCESS 04.00.0000 -
C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp
SELECT PLANT, CAPA AS CAPACITY FROM transp_capa
Reading markets...
Connected to ACCESS 04.00.0000 -
C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp
SELECT MARKET, DEMAND FROM transp_demand
Reading dist...
Connected to ACCESS 04.00.0000 -
C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp
SELECT LOC1, LOC2, DIST FROM transp_dist
Generating cost...
Generating supply...
Generating demand...
Model has been successfully generated
GLPK Simplex Optimizer, v4.42
6 rows, 6 columns, 18 non-zeros
Preprocessing...
5 rows, 6 columns, 12 non-zeros
Scaling...
 A: min|aij| = 1.000e+000  max|aij| = 1.000e+000  ratio = 1.000e+000
Problem data seem to be well scaled
Constructing initial basis...
Size of triangular part = 5
      0: obj =  0.000000000e+000  infeas = 9.000e+002 (0)
*     4: obj =  1.734750000e+002  infeas = 0.000e+000 (0)
*     7: obj =  1.332000000e+002  infeas = 0.000e+000 (0)
OPTIMAL SOLUTION FOUND
Time used:   0.0 secs
Memory used: 0.1 Mb (133559 bytes)
Writing result...
Connected to ACCESS 04.00.0000 -
C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp
DELETE FROM transp_result
INSERT INTO transp_result VALUES (?,?,?)
Model has been successfully processed
>Exit code: 0    Time: 0.412

transp_odbc_mdb.rar
Description: application/octet-string