8 Replies Latest reply: May 15, 2014 10:03 AM by jimk _ RSS

    Scripting Project Exports

    jimk _

      I'm having an issue where my script will not execute my project exports when the export, in particular a summary export, is adding to an existing .mdp or .xls.  I haven't narrowed down the exact issue, but this is what I do know:

       

      1. The project exports work fine when I manually execute them

      2. The script execution of the project exports work fine when I have the project export overwrite the .mdb. or .xls file

      3. The script execution of the project export will not work if I specify the project name on the export switch (px(export name) vs pxall) and have the summary export add to an existing .mdb or .xls file

      4.  If the script is executing a summary export to an existing file and also is executing a table export from the same project, the summary export "failure" prevents the table export for executing as well.

       

      What is wierd is that when I run the script, it opens monarch & builds that tables and queue everything for the export but the summary simply doesn't want to write to an existing file.  Any one have any ideas?

        • Scripting Project Exports
          Grant Perkins

          Jim, welcome to the forum.

           

          Are you running V9.00 or 9.01?

           

          Is the output file you are writing to have a fully qualified path in your script?

           

          Could you post an example of the script line that should be producing the output? I seem to recall that some of the switches are a common source of conflict in use - it's easier to check a line and comment than it is to write out a description. Things also has more relevance that way!

           

          Have you already run a search through the forum posts and been able to eliminate any of the previous suggestions? It will save time if we don't have to follow along those paths.

           

          Hopefully identifying the problem won't take long.

           

          Grant

            • Scripting Project Exports
              jimk _

              Grant,

               

              Thank you for replying.  I'm using Monarch 9.01.  I've looked through the forum for similar problems but haven't found one that addresses my issue. 

               

              I've listed my script paths and what they do: 

               

              Monarch /prj:"
              imco\staff\fund_operations\Accurate\Jim\Scripts\New TBA\New\00.TBA MF Cross Lookup.xprj"  /pxall

              --This one executes a table export that appends a .mdb and overwrites a table (works fine)

               

              Monarch /prj:"
              imco\staff\fund_operations\Accurate\Jim\Scripts\New TBA\New\01.ID Contra Acct on 99.xprj"  /pxall

              --This one executes a table export that appends a .mdb (the same one as above) and overwrites a table (works fine)

                   

              Monarch /prj:"
              imco\staff\fund_operations\Accurate\Jim\Scripts\New TBA\New\02. ID Highest Record.xprj"  /pxall

              --This one executes a summary export that appends a .mdb (the same one as above) and overwrites a table and then executes 7 more summary exports that appends both the .mdp and the table the 1st summary export overwrote.  Then it executes a table export that appends the same .mdb but overwrites a table (works fine)

                 

              Monarch /prj:"
              imco\staff\fund_operations\Accurate\Jim\Scripts\New TBA\New\03.ID Contra Acct on 99(2nd Pass).xprj"  /pxall  

              --This one executes a table export that appends a .mdb (the same one as above) and overwrites a table (works fine)

               

              Monarch /prj:"
              imco\staff\fund_operations\Accurate\Jim\Scripts\New TBA\New\04.ID New A-B TBA.xprj"  /pxall

              --This one is suppose to append a summary export to an existing .mdb (same one as above) and overwrite a table but it doesn't work with the script (it works when I manually execute it).  The only thing I did different on this one is that I changed the column heading properties on the summary, changed the Export and Clipboard Options to export the Column titles and I have a sum measure on a field.  Could changing the Column properties be the problem?

                • Scripting Project Exports
                  Grant Perkins

                  Jim,

                   

                  Yep, if I have understood you correctly I suspect that the Access update will not like those changes at all, seeing them as fundamental attempts to redefine the database structure - or more likely just finding they don't match the definition and so the update is [URL="http://en.wiktionary.org/wiki/give_the_elbow"]'given the elbow'[/URL].

                   

                  If you revise the export to create a new file (as a test) I assume the export would work?

                   

                   

                  Grant

                    • Scripting Project Exports
                      jimk _

                      If I script the export to overwrite an existing file, then it works.  If I manually export the script to an existing file, then it works.  If I script the export to an existing file, then it does not work.  I'm failing to understand why the export behaves differently when I script the execution vs manual execution?  My only guess is that changing the field properties in the summary table causes the summary export to fail when appending to an existing .mdb but why would the script care about that?

                        • Scripting Project Exports
                          Nick Osdale-Popa

                          (Dumb question) all the column names in your tables/summaries correspond to a column name in your database, yes?

                           

                          I'm assuming so, since you said that if you execute the projects manually, all the projects run successfully.

                           

                          The only thing I can think of is perhaps the exports are executing too rapidly and that the database isn't fully closed upon the execution of the next line in the script - thus the database is 'in use' and can't be written to. Perhaps you can pause between each export to see if you have better success.

                           

                          A quick and dirty method of pausing in a batch file is to use ping:

                          PING  127.0.0.1 -n ## > nul

                           

                          where ## is the number of milliseconds to pause (60000 = 60 seconds).

                           

                          Give that a shot and let us know the results.

                            • Scripting Project Exports
                              Grant Perkins

                              Jim,

                               

                              You're right, it does seem odd if it works running the export manually.

                               

                              It might be worth running a quick review thourhg the processes with 'Log Operations' switched on jst to see what gets reported.

                               

                              If you have not come across that bofore check oout the Help for "Settings" and in particular "Hidden Settings" > "Log Operations".

                               

                              It might be a shot to nothing but you never know.

                               

                              Meanwhile I'll see what else I can find.

                               

                               

                              Grant

                                • Scripting Project Exports
                                  Nick Osdale-Popa

                                  FYI: Here's an alternate batch pause routine and how you would use it in your script file:

                                  @echo off

                                   

                                  Monarch /prj:"
                                  imco\staff\fund_operations\Accurate\Jim\Scr ipts\New TBA\New\00.TBA MF Cross Lookup.xprj" /pxall

                                  sleep 10

                                   

                                  Monarch /prj:"
                                  imco\staff\fund_operations\Accurate\Jim\Scr ipts\New TBA\New\01.ID Contra Acct on 99.xprj" /pxall

                                  sleep 10

                                   

                                  Monarch /prj:"
                                  imco\staff\fund_operations\Accurate\Jim\Scr ipts\New TBA\New\02. ID Highest Record.xprj" /pxall

                                  sleep 10

                                   

                                  Monarch /prj:"
                                  imco\staff\fund_operations\Accurate\Jim\Scr ipts\New TBA\New\03.ID Contra Acct on 99(2nd Pass).xprj" /pxall

                                  sleep 10

                                   

                                  Monarch /prj:"
                                  imco\staff\fund_operations\Accurate\Jim\Scr ipts\New TBA\New\04.ID New A-B TBA.xprj" /pxall

                                   

                                   

                                   

                                  GOTO :EOF

                                  :: -


                                  SLEEP ROUTINES -


                                  :sleep

                                       SETLOCAL

                                       SET _delay=%1

                                       IF "%1" == "" SET _delay=10

                                       CALL :sleep_settime

                                       SET /A target=(hr * 3600 + _mn * 60 + _sc + _delay) %% 86400

                                   

                                  :sleep_loop

                                       CALL :sleep_settime

                                       SET /A now=hr * 3600 + _mn * 60 + _sc

                                       IF %_now% GEQ %_target% GOTO sleep_exit

                                       GOTO sleep_loop

                                   

                                  :sleep_exit

                                       ENDLOCAL

                                  ::     CLEARING VARIABLES

                                       FOR %%G IN (_delay _target _now _hr _mn _sc) DO (SET %%G=)

                                       GOTO :EOF

                                   

                                  :sleep_settime

                                  ::     parse time into hours minutes seconds

                                       FOR /F "tokens=1-3 delims=: " %%G IN ("%TIME%") DO (SET _hr=%%G & SET _mn=%%H & SET _sc=%%I)

                                       GOTO :EOF

                                  :: -


                                  SLEEP ROUTINES -


                                  /code

                                   

                                  Alternatively, if you start each monarch export with [i]start /w[/i][/b] and disregard the sleep method entirely, this will execute Monarch so that the next line in the script does not start until the prior instance of monarch is finished.

                                  • Scripting Project Exports
                                    jimk _

                                    I think I know what the issue is...some of my projects are accessing the same .mdb that the same project is exporting to (albeit to a different table within the same .mdb) and I think the script sees the .mdb as being locked and won't complete the export.