$title Renaming Fields (GDX2ACCESSExample5,SEQ=129)

$onText
This example demonstrates how to rename the columns in an Access database file
by running a VBscript after dumping the data using GDX2ACCESS.

This model is referenced in the "Renaming Fields" example from the GDX2ACCESS
Documentation.

Keywords: GDX2ACCESS, data exchange, GAMS language features
$offText

$callTool win32.msappavail Access
$if errorlevel 1 $abort.noError "No Access available"

Set
   i 'canning plants' / seattle,  san-diego /
   j 'markets'        / new-york, chicago, topeka /;

Parameter
   a(i) 'capacity of plant i in cases'
        / seattle    350
          san-diego  600 /

   b(j) 'demand at market j in cases'
        / new-york   325
          chicago    300
          topeka     275 /;

Table d(i,j) 'distance in thousands of miles'
              new-york  chicago  topeka
   seattle         2.5      1.7     1.8
   san-diego       2.5      1.8     1.4;

Scalar f 'freight in dollars per case per thousand miles' / 90 /;

Parameter c(i,j) 'transport cost in thousands of dollars per case';
c(i,j) = f*d(i,j)/1000;

* export parameter c to gdx file.
execute_unload 'c.gdx', c;

* move to access database
* the column names are i and j by default
execute 'gdx2access c.gdx > %system.nullfile%';

* rename columns using the VBscript
execute 'cscript access.vbs > %system.nullfile%';

* write the VBscript at compile time before running the script at execution time
$onEcho > access.vbs
'this is a VBscript script
WScript.Echo "Running script: access.vbs"
set oa = CreateObject("Access.Application")
set oDAO = oa.DBEngine
Wscript.Echo "DAO Version: " & oDAO.version
Set oDB = oDAO.openDatabase("%system.fp%c.accdb")
Wscript.Echo "Opened : " & oDB.name
Set oTable = oDB.TableDefs.Item("c")
Wscript.Echo "Table : " & oTable.name
' rename fields
oTable.Fields.Item("i").name = "ifrom"
oTable.Fields.Item("j").name = "jto"
oTable.Fields.Item("Value").name = "transportcost"
Wscript.Echo "Renamed fields"
oDB.Close
Wscript.Echo "Done"
$offEcho