6 Replies Latest reply: May 15, 2014 10:07 AM by Grant Perkins RSS

    Monarch 10.5 dropping records in Access

    AndrewC _

      I was doing a major data extract exercise recently for a Client into an Access d/b from multiple reports (one per Company for each table).

      I am using Win 7/Access 2007 but was using Access 2003 format .mdb files for compatibility with previous exercises.

      To my horror I found that records exported from Monarch were being randomly dropped and did not appear in the resultant Access table.

      As an example an export of 25,105 records resulted in 25,019 in the table.  There are no filters involved.  There is nothing unusual in the dropped records - they just were not there.

      I have been using Monarch since V6 and have never seen this happen before.

      I did some research on the issue as follows :-

       

      1. Exporting the same data to Access 2003 on another machine was perfect - no records dropped.

      2. Exporting the data to an Access 2007-format d/b (.accdb) on the same machine was perfect - no records dropped.

       

      The issue therefore seems to be related to using the old-format mdb in Access 2007, but I don't know if this is a Monarch or an Access issue.

       

      Anyone else had this experience, or care to throw any light on it?

       

      Regards

      Andrew

        • Monarch 10.5 dropping records in Access
          Grant Perkins

          Hi Andrew,

           

          This is by nomeans an area of expertise for me but my understanding i s that Access 2007 is very different to what went before and I have seen reports of a number of anomalies when converting or using non 2007 sources in 2007. Many issues have been hot fixed or SP's ate various times.

           

          I seem to remember one observation that suggested reading tables from older versions into 2007 could drop the last record in a table, plus a few others that mentioned things like filter and so on as potential problem areas. But, as stated above, I am no expert. I'm guessing that Monarch would not be re-inventing the Access wheel here so any outstanding Access issues could also be reflected in inputs and outputs through Monarch. I assume you have updated 2007 with the latest SP release and patches - if not it is probably a good idea even if only to eliminate as much as possible from the Access 2007 side of things - though that does not guarantee that all inter-version problems have been addressed and resolved.

           

          I hope you find a solution to this but in the meantime I assume you are using a work around of some sort. Is that something you can share here in case others hit the same problem?

           

           

          Grant

            • Monarch 10.5 dropping records in Access
              AndrewC _

              Hi Grant

               

              Good to hear from you again, and thanks for taking the time to reply.

               

              I'm using Access 2007 (12.0.6423.1000) SP2 but I'll check if there are any newer updates.

               

              The only workaround I can find at present is to use the newer Access 2007-format accdb.  That seems to work OK, but I have no idea why.

               

              Since Monarch exports to .mdb without even opening Access I am wondering if it is something in the .mdb format that Access 2007 now expects to see that was not there before.  Perhaps the Monarch Developers could have a look at this.

               

              Regards

              Andrew

                • Monarch 10.5 dropping records in Access
                  Olly Bond

                  Hello Andrew,

                   

                  Monarch traditionally used the Microsoft JET database engine, and is starting to use the replacement ACE engine. Perhaps your issue is connected to this - but please could you confirm the version of Monarch you're using? Also, it would be helpful to check that the latest .NET framework was installed.

                   

                  Just to be clear - I presume the source data is a report, that you've modelled, and Monarch shows x records in the table window, but when exporting to an Access 2003 table only y records appear? Have you tried making a null filter like Recno()>0 and applying this to the export, to force Monarch to filter the records before exporting them?

                   

                  Best wishes,

                   

                  Olly

                    • Monarch 10.5 dropping records in Access
                      AndrewC _

                      Hi Olly

                       

                      I'm using Monarch 10.5 under Windows 7 Pro 64-bit.

                       

                      I think the .NET framework is installed by default in Win 7, in any event it does not show in Control Panel, Programs and Features.

                       

                      My system is set to download MS Updates automatically although I install them manually at a time to suit, so I assume any .NET patches would be there.

                       

                      >Just to be clear - I presume the source data is a report, that you've modelled, and Monarch shows x records int he table window, but when exporting to an Access 2003 table only y records appear? Have you tried making a null filter like Recno()>0 and applying this to the export, to force Monarch to filter the records before exporting them?

                       

                      You are correct in the above assumptions.  NO, I have not tried a filter like that. I'll need to get it to misbehave again then I can try it.

                       

                      From what you say about the engines used is Monarch effectively changing to the ACE engine when exporting to an accdb as opposed to an mdb?

                       

                      Regards

                      Andrew

                        • Monarch 10.5 dropping records in Access
                          Olly Bond

                          Hello Andrew,

                           

                          I saw you'd posted the version 10.5 in the subject line of the thread, after I'd posted my reply /I

                           

                          I can't confirm whether Monarch is using ACE or JET for each export format, as I'm using Office 2003 only here, and have only encountered ACE when diagnosing a strange verification issue in DataPump Pro 10.5. As DataPump was released sometime after Monarch it may well behave differently.

                           

                          Best wishes,

                           

                          Olly

                            • Monarch 10.5 dropping records in Access
                              Grant Perkins

                              Hi Andrew,

                               

                              I half guessed that you would be pretty much up to date with the MS side of things and of course Monarch now you have V10.

                               

                              Of course in MS terms being up to date with patches is not always guaranteed to be a good thing in terms of compatibility but at SP2 something like you describe should really have been resolved if is an inter-version issue.

                               

                              I think I would be tempted to raise the matter with Datawatch support in order to get an offical recommendation. If you can live with the accdb export for your needs then that would seem to be the best solution according to MS. However since there is supposed to be backwards usage compatibility (with some restrictions obviously) according to MS then it seems reasonable that it should all function without errors OR that unavoidable error sutuations, if any, are well documented. MS may already have this information somewhere. If so a link to it would be useful should anyone know of one.

                               

                              Glad you have found a workaround.

                               

                               

                              Grant