I am trying to create an automatic load script, based on data that is stored in different Access databases by year (each year in a different database).
I only want to load the current year and the previous two. The year is indicated in the filename, so I would like the script to use the variable name in both the loop variable (For i=...) and the filename (PathName+i ).
Here is how far I got so far, of course not working correctly.
If you have any idea on how to proceed, I would really appreciate any help.
Let vCurrentYear = Year(Today());
For i = (Num($(vCurrentYear)–2)) To Num($(vCurrentYear))
OLEDB CONNECT32 TO [Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=$(vServerPathName)$(i)& issues report .mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False];
SQL SELECT *
STORE Issues into $(vServerPathName)Issues.qvd;
DROP TABLE Isues;