Description
Import a table into MS Access using VBscript
Category : GAMS Data Utilities library
Main file : PopulateV.gms includes : PopulateV.gms
$onText
Import a table into MS Access using VBscript
$offText
$if %system.filesys% == UNIX $abort.noError 'This model cannot run on a non-Windows platform';
$if exist new.accdb $call del new.accdb
set i /i1*i50/;
alias (i,j,k);
set v /v1*v5/;
parameter p(i,j,k,v);
p(i,j,k,v) = uniform(0,1);
scalar s;
s = sum((i,j,k,v),p(i,j,k,v));
display s;
file f /data.csv/;
f.pc=5;
put f,'i','j','k','v1','v2','v3','v4','v5'/;
loop((i,j,k),
put i.tl, j.tl, k.tl;
loop(v, put p(i,j,k,v):12:8;);
put /;
);
putclose;
* cscript needs to find DAO dll, you might have to call C:\Windows\SysWOW64\cscript.exe
execute "=cscript access.vbs";
executeTool 'win32.shellExecute new.accdb';
$onEcho > access.vbs
'this is a VBscript script
WScript.Echo "Running script: access.vbs"
dbLangGeneral = ";LANGID=0x0409;CP=1252;COUNTRY=0"
strSQL = "SELECT * INTO mytable FROM [Text;HDR=Yes;Database=%system.fp%;FMT=Delimited].[data#csv]"
Wscript.Echo "Query : " & strSQL
Set oJet = CreateObject("DAO.DBEngine.36")
Wscript.Echo "Jet version : " & oJet.version
Set oDB = oJet.createDatabase("new.accdb",dbLangGeneral)
Wscript.Echo "Created : " & oDB.name
oDB.Execute strSQL
Set TableDef = oDB.TableDefs("mytable")
Wscript.Echo "Rows inserted in mytable : " & TableDef.RecordCount
oDB.Close
Wscript.Echo "Done"
$offEcho