22 Replies Latest reply: May 15, 2014 10:16 AM by RalphB _ RSS

    Updating V12 Model with Access Database as source and several database lookups

    DataExploiter _

      I have a model set up in V12 Modeler.  The model retrieves and access database, several lookups to Access and Excel files along with calculated fields.  My question is as follows. I need to update/change location of  the source database and external lookup tables.

       

      How can I keep the original model which knows what fields to include in the main database , external lookup tables, custom calculated fields and formatting? This was possible in prior versions. I could change the location of the files in the model, the calculated fields and fields selected did not change.

      Any thoughts?

       

      Going forward what would be the best source to research questions.

       

      Thanks

        • Updating V12 Model with Access Database as source and several database lookups
          Olly Bond

          Hello Data Exploiter,

           

          For the moment (see AlexM's comments in the Monarch v12 thread) this is a bit clumsy. Save a project as dprj and model as dmod. Close All, then rename the source database (in the file system), e.g. from source.mdb to spare.mdb. Now open the project.dprj file in Monarch, and you should be prompted to browse to find the missing source table.

           

          Best wishes,

           

          Olly

            • Updating V12 Model with Access Database as source and several database lookups
              DataExploiter _

              Olly, I couldn't get it to work. The external lookups would also need updating. Oh and I was unable to find AlexM’s topic comments.

               

              I’m thinking all I need to do is rename, delete or copy to a new folder all the original MDB and XLS files used in the project. Then open the original project created and I will be prompted to find the new source file along with each of the external lookup tables since they will be in different folders or renamed. Then I just point to the new updated files.

               

              It must be me. It’s late[/B]. The extra fields in V12 were being used and the performance was nice but may need to use V11 with work around.

               

              Thanks for all the help! Tomorrow I will try the approach of just relocating the files. John

                • Updating V12 Model with Access Database as source and several database lookups
                  Olly Bond

                  Hello John,

                   

                  Alex's posts are here: http://www.monarchforums.com/showthread.php?4543-Monarch-Version-12/page3[/url]

                   

                  External lookups can be edited in Table Design. It's just changing the path to the source database that requires the workaround.

                   

                  Best wishes,

                   

                  Olly

                    • Updating V12 Model with Access Database as source and several database lookups
                      DataExploiter _

                      The database path to external lookups can be viewed through table design. It cant be changed, just viewed. I'm still dont get promoted for the main database even after changing the file name. Nothing . any thoughts on what to try other than V11? Thanks John

                      • Updating V12 Model with Access Database as source and several database lookups
                        DataExploiter _

                        Just an additional comment, when you point to an Access Database and the file contains more than one database it will not let you choose the desired database. In V11, 10 and prior you could select the MDB file that contains multiple databases and then chose the database within the mdb file you wish to join. I'm still working with my model that needs this function. The more I see/find I will point them out in hopes they get corrected. Rebuilding and getting use to Modeler. Thanks John

                          • Updating V12 Model with Access Database as source and several database lookups
                            DataExploiter _

                            When doing external lookups and not being able to point to specific databases within one MDB file is forcing me to go back to V11 or try rebuilding. Unfortunately my databases and multiple models get updated often with different names within the same MDB file or files.

                              • Updating V12 Model with Access Database as source and several database lookups
                                Olly Bond

                                Hello John,

                                 

                                I've reported the issue to the developers. I hope that it can be fixed in the next release.

                                 

                                Best wishes,

                                 

                                Olly

                                  • Updating V12 Model with Access Database as source and several database lookups
                                    ARICE01 _

                                    I continue to struggle with V12 (Modeler) due to LOSS of expected, simplistic functionality from prior Monarch Pro releases (most recently v11.8).  The ability to easily switch data sources (database and lookups) within projects/models during active sessions have been lost.  This forum did provide an effective, albeit cumbersome workaround (involving renaming the original database/lookup sources which forces Modeler to prompt for the source/lookup data source) but I really hope Datawatch soon include this functionality in a patch/upgrade.  I am religiously reading the Modeler documentation and dont think I am missing anything.  Has anyone come up with a quicker way to switch source data during active project sessions?  When installing Modeler, I had to uninstall v11.8, so going back is not an efficient option for me now.

                                     

                                    Datawatch, please please give me some relief here soon!!!

                                      • Updating V12 Model with Access Database as source and several database lookups
                                        Olly Bond

                                        Hello Arice,

                                         

                                        v11 and v12 should co-exist peacefully, as far as I'm aware. You might have some odd associations for file extensions, but I don't think the two should conflict. I'll double check. But yes, this behaviour is a hassle, and I'd hope it should be a fairly easy one to fix.

                                         

                                        Best wishes,

                                         

                                        Olly

                                        • Updating V12 Model with Access Database as source and several database lookups
                                          DataExploiter _

                                          I agree its important to have the  "ability to easily switch data sources (database and lookups) within projects/models during active sessions". This fix should be given priority.

                                            • Updating V12 Model with Access Database as source and several database lookups
                                              Joe Berry

                                              I agree with the priority of this issue.

                                               

                                              I am still getting used to v12.  I owned v11x but never used it. I felt it was too resource intensive and not as stable as v10.5.  I feel v12 is much less resource intensive and more stable, but there has to be a quick fix to this problem.  I find there are a few tweaks that could make this version much more user friendly, but none that have this level of impact.

                                                • Updating V12 Model with Access Database as source and several database lookups
                                                  Grant Perkins

                                                  Hi All,

                                                   

                                                  It's probably worth bearing in mind that in V12 we are now working with an entirely different underlying 'engine' . Things that could be done with the Access engine do not necessarily have direct equivalents with an SQL DB. Maybe nothing close to the same. And there is much more effort required to tune the DB and interfacing to the DB for performance as well as fast volume handling whilst at the same time ensuring that no unsuspected, unhandled situations with, say, internal memory management as large database sets are swapped around in an active session.

                                                   

                                                  Whatever development can be created it has to be entirely reliable in that context in all likely user system specification environments.

                                                   

                                                  It should be a good challenge for the team.

                                                   

                                                   

                                                  Grant

                                                    • Updating V12 Model with Access Database as source and several database lookups
                                                      AlexM _

                                                      Sympathies all round here!

                                                       

                                                      I certainly hope that a further release of v12 incorporates the functionality of previous versions of Monarch and especially the ability to easily deal with both Excel and Access lookups otherwise - for me at least - it's questionable whether it will have continued use and/or value for our company.

                                                       

                                                      Although it's not perfect, I've found v11.8 to be fairly stable, workable and, quite usefully, more able to deal with larger datasets than v10.5 which throws out a few "internal error" messages when pushed. (That said, I do find the simpler navigation in v10.5 quite refreshing and occasionally more responsive.)

                                                       

                                                      Alex

                                                • Updating V12 Model with Access Database as source and several database lookups
                                                  DataExploiter _

                                                  Just a comment that may be helpful to improve V12. When the database and lookups are changed to force a prompt for a project or model it loses all the prior rules for number of decimals, alignment. This forces updating the prior formats. Is there a central source for all documentation for V12 other than help topics. Thanks John

                                                    • Updating V12 Model with Access Database as source and several database lookups
                                                      ARICE01 _

                                                      All:  I am becoming increasingly frustrated with v12.  To get around the lost capabilities to easily switch data sources and automation (using command files), I tested using External Lookups where the structure of the Excel files were idential (field and row names), then tried to see how many External links Modeler would allow me to use.  I was able to get up to 17 links (although the documentation says 9 is the maximum).  This was a good thing since v11.8 strictly enforced the 9 link maximum.  Then I tried to get fancy and edit the original data source to create a new project/model to leverage all my effort to create copies of the project/model for other, similar data. I failed... and had to completely create a new project/model going through all these steps again.  While v11.8 was limiting with the 9 External Lookups, the ability to create duplicates and easily swith data sources provided greated productivity.  I regret upgrading to Modeler so quickly, without having v11.8 as a backup.  I will struggle through, but my patience is very thin... I really wish there was another way.

                                                        • Updating V12 Model with Access Database as source and several database lookups
                                                          Olly Bond

                                                          Hello ARICE,

                                                           

                                                          16 lookups, as well as the original table, is the spec, and it's delivered - the documentation is perhaps lagging behind. But the release notes are clear! In practice, I've only ever twice needed to join more tables together than that.

                                                           

                                                          Automation was gone in 11.8 - if you still rely on desktop automation that v11.5 or earlier would be appropriate.

                                                           

                                                          Manually joining tables of data on a regular basis seems like a cumbersome approach - can the data not be downloaded with consistent filenames?

                                                           

                                                          Best wishes,

                                                           

                                                          Olly

                                                            • Updating V12 Model with Access Database as source and several database lookups
                                                              ARICE01 _

                                                              Hi Olly:

                                                               

                                                              Yes manually joining tables are very cumbersome and undesireable.  What I have are Excel files with identical formats (Column Namesand Row Names) on a SharePoint server.  These files are active and available for others to update.  In this case, I am trying to periodically consolidate the files without having to touch these files.  Downloading as many files creates an undesireable step.  If I could handle Excel files similar to Reports with a standard formats (i.e., stacking them on top of each other) would be a great.  Or if I could put Modeler into a loop, and export/append the data would also work.  Using the External link approach, and using multiple Project/Models seems the best option for me. I will limp along and hope for a breakthrough someday.

                                                               

                                                              Thanks for you advice...

                                                              Al

                                                      • Updating V12 Model with Access Database as source and several database lookups
                                                        DataExploiter _

                                                        Olly

                                                         

                                                        Thanks, late reply but thanks. The entire process should duplicate prior versions for monarch to open database then database lookups save this as project/model. Then try to open that project or model and point to new database files. I was just trying again in V12 and no luck. It will work in prior versions so they can see how it should work. Not sure if that helps or confuses the issue. John

                                                          • Updating V12 Model with Access Database as source and several database lookups
                                                            ARICE01 _

                                                            All:

                                                             

                                                            Regarding the inability to change source data sources as the primary database or External Lookups, this is a continued source of frustration for me.  Having learned about a work-around here (renaming files which forces Modeler to prompt for new files) as helped.  There is one aspect of the work-around that I wanted to point out when sourcing data from Excel files.  I tried to be fancy and take the file renaming a step further by also changing the name of the Excel tables (worksheerts) and that defeated the work-around and forced me to go back and rename the Excel tables (worksheets).  Again, Modeler will prompt you for an alternative file when the original file is no longer found, but it does not allow you to reference the relevant worksheet if that name has also been changed. 

                                                             

                                                            If other have encountered this, please confirm, or tell me if I am off base.   Lets continue to collaborate on Modeler (v12.x) as there is so much lost from previous versions of Monarch.