0 Replies Latest reply: Aug 2, 2013 8:46 AM by Gabor Torma RSS

    automatic load loop from multiple files by year

      Hello,

       

      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());

       

      Sub LoadData

       

      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];

       

      Issues:
      LOAD

           Field1,

           Field2,

           Field3 ;
      SQL SELECT *
      FROM Issues;

       

      Next i

       

      STORE Issues into $(vServerPathName)Issues.qvd;
      DROP TABLE Isues;

      End Sub;

       

      Call LoadData;