19 Replies Latest reply: May 15, 2014 10:12 AM by Rich R RSS

    Monarch 11 and Access 2003

    Timtropolis _

      Greetings all,

       

      We have just upgraded from Monarch 8 to Monarch 11.  Our environment is Windows XP (SP2), Office 2003.

       

      In testing our automation, everything works fine until we try to write out to the database (utilizing .jetExportTable[/B]).  When we do, Monarch makes a copy of the existing database (.mdb, aka Access 2003) to an .ACCDB (Access 2007).  So what we have is TestMonarch_DB.mdb.ACCDB[/B].  I've looked for a "switch" or flag within the options/preferences that might force .mdb usage and saw an option for legacy databases (defaulted to Access 2000) but did not see anything regarding ACCDB.  Is it possible this is some sort of registry setting? 

       

      Also, when the writing out to the ACCDB I noticed that the ACCDB would reset in size, so it is either deleting the data in existing tables or recopying the database with each pass. 

       

      I've looked for an updated programmers guide to see if there were any new methods, etc, but from what I read on the forum, the programmers guide never got updated.

       

      Can someone shed some light on this?

       

      TIA

      Tim

        • Monarch 11 and Access 2003
          RalphB _

          Hi Tim and welcome to the forum.

           

          Since you are using Office 2003 you will need to set your export default settings in Monarch to use Access 2000.  Microsoft Office is backward compatible to older versions of their products but not forward compatible so Office 2003 cannot read any Office 2007 or newer files which the .ACCDB files are.

           

          Give that a try.  You should have no problems once you do.

           

          Ralph

            • Monarch 11 and Access 2003
              Timtropolis _

              Good morning Ralph,

               

              Thanks kindly for your reply.

               

              The export settings are already defaulted to Access 2000 but the problem still persists. 

               

              As an fyi, I have contacted my tech support to make sure there were no attempts to place Office 2007 on my computer and as I expected,  there were no such attempts.  That being said, I have also checked the registry for references to Office 2007/2010.  I did find within the registry editor references under HKEY_LOCAL_MACHINE a reference to Office 14.0 and Office 12.0.  Under 14.0, there is an entry for Access connectivity engine.  Not sure if this has any bearing on this issue, but thought I’d mention it

               

              I have contacted Monarch support with regards to this as well so hopefully they can provide some info as well.

               

              Tim

                • Monarch 11 and Access 2003
                  elginreigner _

                  Good morning Ralph,

                   

                  Thanks kindly for your reply.

                   

                  The export settings are already defaulted to Access 2000 but the problem still persists. 

                   

                  As an fyi, I have contacted my tech support to make sure there were no attempts to place Office 2007 on my computer and as I expected,  there were no such attempts.  That being said, I have also checked the registry for references to Office 2007/2010.  I did find within the registry editor references under HKEY_LOCAL_MACHINE a reference to Office 14.0 and Office 12.0.  Under 14.0, there is an entry for Access connectivity engine.  Not sure if this has any bearing on this issue, but thought I’d mention it

                   

                  I have contacted Monarch support with regards to this as well so hopefully they can provide some info as well.

                   

                  Tim[/QUOTE]

                   

                  If you would remove these entries from the registry they will come back when you update your Office next. I don't recall when, but office 2003/2007/2010 share updates that add in registry entries. This should not be affecting your Monarch export.

                   

                  Also, I recommend updating your PC if you are only on SP2 for XP. I'm all IT here, forgive me if I over stepped my bounds.

                    • Monarch 11 and Access 2003
                      Timtropolis _

                      Hi Elgin,

                       

                      Thanks for responding and no worries about over-stepping.

                       

                      Unfortunately, I don't have the authority to do much to my PC.  I'm a consultant and have no admin rights, plus, IT has a pretty heavy hand as it is regarding PC setup.

                       

                      To update, I have gotten feedback from Monarch support and have provided them with my code and scenario.  When I hear back from them, I will hopefully be able to update this thread with a solution.

                       

                      Thanks again

                        • Monarch 11 and Access 2003
                          Timtropolis _

                          Update:

                           

                          Greetings all,

                           

                          Here is the response I just got  from Monarch "level 2" support:

                           

                          The command line options for Monarch Professional are no longer documented since they have been deprecated and will be phased out in future versions.

                           

                          The new Monarch Professional versions have too many options it is no longer practical to expand the command line syntax in the clean, simple and consistent interface characteristic of Monarch products. We note that except for console applications, most Windows programs no longer support extensive command line options.

                           

                          In summary, the Monarch command line options are deprecated beginning in version 9, and should be avoided. They exist in recent versions in order to provide backward compatibility, and to give programmers who used them some time to migrate their automation scripts to recommended methods.

                           

                          /I

                           

                          Not exactly a ringing endorsement for their level two support considering the line above was followed by another 3 paragraphs trying to sell me on "Data Pump".

                          Reading between the lines, just seems like they don't want to investigate or perform due diligence on their product.

                           

                          FYI, just to let everyone know, here is the piece of code which is causing me issue

                           

                          .JetExportTable(strExportMDBFullFileName, strExportMDBTableName, intJetExportAppendOption)[/B]

                           

                           

                           

                          The argument variables are:

                            strExportMDBFullFileName = "C:\Databases\Monarch\MonarchImportTMP_BE.mdb"

                            strExportMDBTableName = "NITB"

                            intJetExportAppendOption = 2[/B]

                           

                          If anyone can provide some hindsight or a better way to accomplish, I'm all eyes/ears.

                           

                          Thanks again

                          Tim

                            • Monarch 11 and Access 2003
                              RalphB _

                              Tim,

                               

                              Are talking about command line or OLE programming?  The .JetExportSummary is part of the OLE programming and not part of the command line functions.

                                • Monarch 11 and Access 2003
                                  Timtropolis _

                                  Tim,

                                   

                                  Are talking about command line or OLE programming?  The .JetExportSummary is part of the OLE programming and not part of the command line functions.[/QUOTE]

                                   

                                  Hi Ralph,

                                   

                                  I was referring to OLE programming.  We have used Access 2003 in conjunction with Monarch 8 for the last 4 years or so to automate the process of taking files/reports from out mainframe servers, formatting them, and then pushing them into our Access database tables.

                                   

                                  Sorry if I wasn't clear enough in my original post. 

                                   

                                  Kind regards,

                                  Tim

                                • Monarch 11 and Access 2003
                                  Olly Bond

                                  Hello Tim,

                                   

                                  I'm no programmer, so I try to avoid wading into threads like these, but Monarch 11 doesn't use the JET database engine but the newer Access database engine from Microsoft, so it may be that it can't support a method that makes reference to JET. Can you find an alternative ExportTable method?

                                   

                                  As for the comments from support, firstly, I'd like to thank you for posting them here so we can share them. Historically, Monarch came in Standard and Professional editions, both of which supported automation through COM, but Standard didn't handle external lookups, HTML tables, PDF import, and project files. The price difference was marginal (15%?) so most users went for Professional. There were also lots of licensing models, as well as Monarch Report Explorer as a mini desktop client for reports where someone else had developed the model.

                                   

                                  With v11, it looks like Datawatch have positioned Monarch Professional as the single desktop development tool for models, and are encouraging users to deploy Monarch to model builders, and then to automate processes on the server with DataPump, and deliver reports to users via Monarch BI Server. This makes some sort of sense - I'm lucky to work in an organisation where we don't run our processes with scripts or batch files, but have a server with DataPump where we can keep log files, and handle email and Sharepoint distributions, and we also use the document-level access controls in BI Server to deliver other reports to users through the web interface.

                                   

                                  But I do know from many users here that not everyone is lucky enough to get their IT department to support report mining, or to have the budget, and staff, to deploy DataPump server, so I wonder whether Datawatch should consider releasing a desktop version of Monarch (perhaps from v12) which does support automation? I'm sure that there would be many users who would be willing to pay a premium to be able to script Monarch processes, but who can't make the leap to a full server deployment.

                                   

                                  Best wishes,

                                   

                                  Olly

                                    • Monarch 11 and Access 2003
                                      RalphB _

                                      If Monarch's intention is to get people to move to DataPump that leaves us out.  We will have to keep using old versions of Monarch or resort to re-writing all out scripts to extract data out of reports another way.

                                       

                                      It isn't IT that is the one refusing, it is management.  Their logic is if they are going to spend the money for DataPump, I have been told they would rather put the money into programming.  I'm sure that would go to paying a premium too.

                                       

                                      I am sure there are quite a few Monarch users that are in my shoes.  So if Monarch mangagement is listening you will be losing some long term customers if do not support automation.

                                        • Monarch 11 and Access 2003
                                          elginreigner _

                                          If Monarch's intention is to get people to move to DataPump that leaves us out.  We will have to keep using old versions of Monarch or resort to re-writing all out scripts to extract data out of reports another way.

                                           

                                          It isn't IT that is the one refusing, it is management.  Their logic is if they are going to spend the money for DataPump, I have been told they would rather put the money into programming.  I'm sure that would go to paying a premium too.

                                           

                                          I am sure there are quite a few Monarch users that are in my shoes.  So if Monarch mangagement is listening you will be losing some long term customers if do not support automation.[/QUOTE]

                                           

                                          I agree. I just sent this to my sales rep. Everything we do here once the models are written is via batch processing (DOS or VBS). This is sad news if it ends up being true.

                                            • Monarch 11 and Access 2003
                                              arceecola _

                                              Hi all,

                                               

                                              Please don't tell me that's all she wrote! I just recently upgraded to V. 11 from v6.to take advantage of the SetRuntimeParameter method only to find that I can no longer export to our existing 2003 .mdb using .JetExportTable. Our company is not moving toward Office 2007 anytime in the near future, there must be another way!

                                          • Monarch 11 and Access 2003
                                            Timtropolis _

                                            Greetings Olly,

                                             

                                            Thanks for you informative reply, I've addressed key bits down below

                                             

                                             

                                            Monarch 11 doesn't use the JET database engine but the newer Access database engine from Microsoft, so it may be that it can't support a method that makes reference to JET. Can you find an alternative ExportTable method?

                                            Olly[/QUOTE]

                                             

                                            This is exactly what we've had to do, use the ExportTable method.  Unfortunately, this means instead of exporting to Access directly, we have to export to flat txt files, THEN import them to Access.  It works but with the amount of files/data we get, its really bogged down our process.

                                             

                                            As far as the rest of your response, when we asked Monarch a couple weeks ago about upgrading, we did so at their advice which said that our existing code and mods would work.  There was no mention of the changes you have detailed.   As a consultant , I can only advise, but the IT dept here made us upgrade because of licensing issues.  Our IT dept is very strict as the company has just migrated itself to a investment bank and therefore, the regulations of installing software, etc is a pain.  Plus if we have a solution that has worked with V8, they will probably want to just purchase more V8 licences instead.

                                             

                                            Thanks again for your input, much appreciated.

                                             

                                            Tim

                                              • Monarch 11 and Access 2003
                                                Olly Bond

                                                Hello Tim,

                                                 

                                                I think that v10 of Monarch uses the JET engine, and is AFAIK still supported, so perhaps there's an intermediate solution for customers who want to stay with supported software but still depend on script calls to JET methods. It strikes me as a little shoddy that someone advised you that the code would work fine without perhaps thinking through the consequences of what is a fairly major architectural change at the heart of the new version. Monarch 11 uses the ADE engine - not just to handle more data and faster speeds, but because there is no 64-bit version of the JET engine.

                                                 

                                                Best wishes,

                                                 

                                                Olly

                                                  • Monarch 11 and Access 2003
                                                    RalphB _

                                                    Olly,

                                                     

                                                    Would the 32 bit Monarch 11 handle the jet engine?

                                                      • Monarch 11 and Access 2003
                                                        Grant Perkins

                                                        Ralph,

                                                         

                                                        From what I can gather of the wider marketplace and looking at the offerings of the mainstream PC vendors (Say Dell to take a very public and international example) 64 bit seems to be the way everything is moving. All types of applications are becoming very memory hungry, many applications are architected (that's one for the OED I reckon!) to work with ever large files and people demand instant results from something that a generation ago would have been a miracle if it could be processed in less than week - or at all. Plus for many applications (non-business data manipulation for the most part) Apple are seen as the boys to beat. The iPhone has given tham a 'popular' presence, especially amongst decision makers, and people expect high-end Apple-like performance (whatever that means to them) from a range of personal tools.

                                                         

                                                        MS will need to at least keep up. 64bit seems to be a major part of the strategy.  Not so 32bit.

                                                         

                                                        It's a question of 'If not now, when?" if discussing system migrations.

                                                         

                                                        It occurs to me that the timing of this particular discussion thread is rather useful. It should allow plenty of time for some discussion and exchange of relavant views and information about the technological issues prior to the revived Conference and anything discussed and shared there may well be useful input for the next period of Monarch development.

                                                         

                                                        Here in the UK I often think that management approach such issues from a position of elective unenlightenment. Something that entirely misses the point of their decision and reduces it to margin analysis on estimated capital investment when they should be reviewing the wider issues and understanding that the cost of bought in tools vs bespoke coding is not a simple  comparison for many reasons. If they applied the same principles to buying licences for, say, Excel there would be entire armies of IT workers producing bespoke spreadsheet applications from scratch on the basis that 90% of the MS product is never used within the business and so it must be cheaper to 'do it yourself' (and save the cost of the IT licence compliance department ....)

                                                         

                                                        Of course this point of recognition of value has always, in my time using Monarch, been an issue for the unenlightened. 

                                                         

                                                         

                                                        I fully appreciate that my view may be very one-sided but we need balance from somewhere!

                                                         

                                                         

                                                        Grant

                                                          • Monarch 11 and Access 2003
                                                            elginreigner _

                                                            Ralph,

                                                             

                                                            From what I can gather of the wider marketplace and looking at the offerings of the mainstream PC vendors (Say Dell to take a very public and international example) 64 bit seems to be the way everything is moving. All types of applications are becoming very memory hungry, many applications are architected (that's one for the OED I reckon!) to work with ever large files and people demand instant results from something that a generation ago would have been a miracle if it could be processed in less than week - or at all. Plus for many applications (non-business data manipulation for the most part) Apple are seen as the boys to beat. The iPhone has given tham a 'popular' presence, especially amongst decision maker, and people expect high end Apple like performance (whatever that means to them) from a range of personal tools.

                                                             

                                                            MS will need to at least keep up. 64bit seems to be a major part of the strategy.  Not so 32bit.

                                                             

                                                            It's a question of 'If not now, when?" if discussing system migrations.

                                                             

                                                            It occurs to me that the timing of this particular discussion thread is rather useful. It should allow plenty of time for siome discussion and exchange of relavant views and information about the technological issues prior to therevived Conference and anything dicussed and shared there may well be useful input for the next period of Monarch development.

                                                             

                                                            Here in the UK KI often think that management approach such issues form a position of elective unenlightenment. Something that entirely misses the point of their decision and reduces it to margin analysis on estimated capital investment when they should be reviewing the wider issues and understanding that the cost of bought in tools vs bespoke coding is not a simple  comparison for many reasons. If they applied the same principles to buying licences for, say, Excel there would be entire armies of IT workers producing bespoke spreadsheet applications from scratch on the basis that 90% of the MS product is never used within the business and so it must be cheaper to 'do it yourself' (and save the cost of the IT licence compliance department ....)

                                                             

                                                            Of course this point of recognition of value has always, in my time using Monarch, been an issue for the unenlightened. 

                                                             

                                                             

                                                            I fully appreciate that my view may be very one-sided but we need balance from somewhere!

                                                             

                                                             

                                                            Grant[/QUOTE]

                                                             

                                                            Interesting read. I agree, the end user expects a certain experience with a product based on another product's experience. We're all in this boat, whether its a PC, a car, a previous experience at a grocery store, we've become to expect things that should not be expected.

                                                  • Monarch 11 and Access 2003
                                                    Gareth Horton

                                                    Hi Tim,

                                                     

                                                    It is unfortunate that Datawatch support provided an incorrect response to the issue of automation (COM/OLE and command line) in Monarch 11. 

                                                     

                                                    The misunderstanding has been addressed and will not happen again. We offer our apologies for the concern experienced by long-standing customers.

                                                     

                                                    To respond to the immediate question, neither command line automation, nor COM/OLE automation has been deprecated and is still available in Monarch 11.  There are no plans to discontinue or deprecate this in future versions.

                                                     

                                                    We have discovered a bug in the export functionality (JetExportTable & JetExportSummary methods) which is corrected in the next Monarch 11 point release (11.1), which is imminent.

                                                     

                                                    The update will be available from the Datawatch web site (http://www.datawatch.com/_support/downloads_updates.php[/url]) and free of charge to Monarch 11 customers.  When the update is available to download, we will post a notification on the forum.

                                                     

                                                    The only changes in automation in Monarch 11 are the support for 64-bit Monarch, allowing automation from 64-bit processes and a limitation of one concurrent automation session.  This limit is to provide stability, following information from Microsoft regarding some of the components that we use in Monarch.  For more information, refer to http://support.microsoft.com/kb/257757[/url].

                                                     

                                                    Automated processes will continue to execute as before.  As indicated in the forums, care must be taken to ensure that the correct database engine (consistent with the MS Office version) is being used in all automation scripts.

                                                     

                                                    For clarification - the naming of JetExportTable and JetExportSummary is really a legacy from the need to distinguish the original ExportTable and ExportSummary methods, since the new methods did use the Microsoft Jet Engine as opposed to the internal Monarch export engine and allowed more arguments.  For compatibility reasons we kept the functionality of ExportTable and ExportSummary the same and just introduced new methods. 

                                                     

                                                    Now, some of the JetExportTable/JetExportSummary exports are actually produced by the "internal" engine and some by ACE (Access Database Engine) but the functionality is the same.  The aim has always been to try and ensure compatibility of existing customer code with the introduction of new versions of Monarch.

                                                     

                                                    Gareth

                                                      • Monarch 11 and Access 2003
                                                        Timtropolis _

                                                        Hi Gareth,

                                                         

                                                        Well your response certainly puts me at ease, thanks very much for posting.  Will be looking forward to 11.1 update!

                                                         

                                                        Kind Regards,

                                                        Tim

                                                        • Monarch 11 and Access 2003
                                                          Rich R

                                                          Automated processes will continue to execute as before. As indicated in the forums, care must be taken to ensure that the correct database engine (consistent with the MS Office version) is being used in all automation scripts.

                                                           

                                                          I have the Monarch Pro V11.2 and am doing testing but have not yet received the update from MS Office 2003 to 2010.  Is this causing my bat files to not run or is is just that I am needing to do more work on the bat files due to parts of the Monarch being installed in a different drive?  before everything was on the C drive and now Models/projects/ and the rest will be on the D drive...

                                                          this used to work...

                                                          C:

                                                          CD\Program Files\Monarch

                                                          echo opening Monarch

                                                          Program\Monarch.exe Projects\r137.xprj Export\Autoscript\R137.xls /t

                                                          now I am trying...

                                                          C:

                                                          CD:\Program Files\Datawatch\Monarch

                                                          echo opening monarch

                                                          Program\Monarch.exe "D:\documents and settings\All Users\Documents\Monarch\Projects\FTP_amr137.xprj" export\autoscript\R137_test_Report.xls /t