11 Replies Latest reply: May 15, 2014 10:04 AM by rlandeo _ RSS

    Using Bat file with Access

    kim14anne _

      Hello,

       

      I have been searching the forum and I can't seem to find an answer for this and I am sorry if I missed the thread.

       

      I am trying to create a bat file that will export my Monarch data to a table in access.  The problem I am having is that I want the data to overwrite any data that is in the table currently.  When I set up my project export to overwrite the table it overwrites all of the tables in my access database. So any other tables that I had in the database would be erased.

       

      Any idea how to correct this problem without having to append the table and go in and manually delete the old information?

       

      Thanks

        • Using Bat file with Access
          Data Kruncher

          Hello, and a belated "Welcome to the forum!" to you.

           

          It would be helpful if you could post the contents of your batch file, or at least the important part of it.

           

          I expect that you'll need to add the /exptableopt:add, wait that's incorrect, /exptableopt:[B]append[/B] parameter to add data to a specific table (use /exptable: for that) without replacing the entire database file. You'll need both parameters for the command to function properly.

           

          You might be interested in trying my free utility, the [URL="http://********************/tips/monarch-batch-file-generator"]Monarch Batch File Generator[/URL]. It was inspired by precisely the type of need and challenge that you've described, and seems to be the tool of choice for many in your situation.

           

          HTH,

          Data Kruncher

            • Using Bat file with Access
              kim14anne _

              Here is my bat file:

               

              "c:\Program Files\Monarch\program\Monarch.exe" /prj:"Q:\RCO Analytics\Kim\ARRCreditBalance\$$PXCREDPROJECT.xprj" /exp:"Q:\RCO Analytics\Kim\ARRCreditBalance\ARRCreditBalance.mdb.xls" /table:[$$PXCRED] /pxall /T

               

              the /table:[$$PXCRED]  is prob not needed I was trying to experiment with the exporting.

               

              To explain more this is going to be a weekly database that I want the data from the current week to replace last weeks data.  I do not really want to add to the previous data thought if that is the only choice I can deal with it.  I want to be able to overwrite the data in the table without it overwriting other tables.  I hope this helps.  Kim

                • Using Bat file with Access
                  Data Kruncher

                  "c:\Program Files\Monarch\program\Monarch.exe" /prj:"Q:\RCO Analytics\Kim\ARRCreditBalance\$$PXCREDPROJECT.xprj" /exp:"Q:\RCO Analytics\Kim\ARRCreditBalance\ARRCreditBalance.mdb" /exptable:"$$PXCRED"  /T /exptableopt:overwrite /expfileopt:add[/code]

                   

                   

                  My steps:[LIST=1]

                  I removed a space from the xprj extension. I'm sure that your batch file was fine though.

                  I adjusted the export file from ".mdb.xls" to ".mdb".

                  I added double quotes around the table name.

                  I removed the /pxall as it doesn't apply when exporting from the Table window as the /T directs Monarch to activate the Table window. If you didn't imply that you're attempting to export to a single table, I'd have left the /pxall alone and would have removed the /T.

                  I removed the square brackets [] from the table name as they're not legal for Access table names.

                  Added the /expfileopt:add to append to the existing database.

                  Finally, I added the /exptableopt:_overwrite (without the underscore - the forum converts colon o to :o). You're correct; I missed with the original append recommendation.

                  /LISTHow does that work?

                   

                  Kruncher

                    • Using Bat file with Access
                      kim14anne _

                      Here is the new bat file:

                       

                      "c:\Program Files\Monarch\program\Monarch.exe" /prj:"Q:\RCO Analytics\Kim\ARRCreditBalance\$$PXCREDPROJECT.xprj"  /exp:"Q:\RCO Analytics\Kim\ARRCreditBalance\ARRCreditBalance.mdb" /table:"$$PXCRED" /expfileopt:add /exptableopt:_overwrite /T

                       

                      (I added the underscore so it didn't convert to the smily face)

                       

                      It does appear to be working in regards to not deleting my other table, but the bat file does not seem to be recognizing the table name.  Do you see any other problems with the file, do I have everything in correct order?  (I tried the /table in this spot and after the /exptableopt with the same problem)