Description
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
Category : GAMS Data Utilities library
Main file : GDX2ACCESSExample5.gms includes : GDX2ACCESSExample5.gms
$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