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

    Upgraded to V9 - Automated export to Access broken

    Marcin _

      Hi Everyone,

       

      I just recently upgraded to Monarch Pro V9 from V8.

       

      I have an MS Access application that uses Monarch through VBA.  After the upgrade, the export of the report data seems to fail.  The JetExportTable method returns False.  My code has not changed.  The only difference is Monarch.

       

      I have the V8 programmers guide, but from what support has told me, nothing has changed from V8.  All of my code seems to be consistent with the guide.  The references are fine too.

       

      Does anyone know what might be going wrong?  Please see code attached below.

       

      Thanks!

       

      Private Sub Import_Open_Click()

      DoCmd.OpenForm "Processing"

      Dim db As Database

      Set db = CurrentDb

      Dim MonarchObj As Object

      Dim openfile As Boolean

      Dim openmod As Boolean

      Dim expfile As Boolean

      Dim t As Boolean

      Dim IndirC As String

      Dim IndirH As String

      Dim Filename As String

      Dim MonModOpen As String

      Dim TempDB As String

      Dim i As Integer

      Dim fso

      Set fso = CreateObject("Scripting.FileSystemObject")

      MonModOpen = "G:\Public\FSC-Payables\E08 - PAP & EDI820 Pymts\Open Payables.xmod"

      TempDB = "G:\Public\FSC-Payables\E08 - PAP & EDI820 Pymts\PAP - Monarch.mdb"

      IndirC = "G:\Public\FSC-Payables\E08 - PAP & EDI820 Pymts\Input"

      IndirH = "G:\Public\FSC-Payables\E08 - PAP & EDI820 Pymts\Input\Historic\"

       

                  With Application.FileSearch

                      .NewSearch

                      .LookIn = IndirC

                      .Filename = "Utility Invoices*.pdf"

                          If .Execute > 0 Then

                               

                              'If Monarch is currently active GetObject will use Monarch. If it is not use the CreateObject() to

                              'open another copy of Monarch.

                              Set MonarchObj = GetObject("", "Monarch32")

                              If MonarchObj Is Nothing Then

                                  Set MonarchObj = CreateObject("Monarch32")

                                  t = MonarchObj.SetLogFile("G:\Public\FSC-Payables\E08 - PAP & EDI820 Pymts\PAP-Monarch.log", False)

                              End If

                               

                              For i = 1 To .FoundFiles.Count 'loop for all files found

                               

                                  'Open found files as reports in Monarch

                                  Let Filename = .FoundFiles(i)

                                  openfile = MonarchObj.SetReportFile(Filename, True)

                                  fso.movefile Filename, IndirH

                                                                                      Next i

                               

                          Else:   MsgBox "No new files found.  Ensure that Open AP Details report has been run and PDF file saved in 'G:\Public\FSC-Payables\E08 - PAP & EDI820 Pymts\Input' as 'Utility Invoices*.pdf'."

                                  DoCmd.Close acForm, "Processing", acSaveNo

                                  GoTo Terminate

                               

                          End If

                  End With

       

          openmod = MonarchObj.SetModelFile(MonModOpen)

          If openmod = True Then

              expfile = MonarchObj.JetExportTable(TempDB, "Open Invoices", 0)

                  If expfile = False Then

                  MsgBox "Export Failed."

                  GoTo Terminate

                  End If

          Else: MsgBox "Model not open."

          GoTo Terminate

          End If

           

          MonarchObj.CloseAllDocuments

          MonarchObj.Exit

           

          DoCmd.SetWarnings False

          DoCmd.DeleteObject acTable, "Open Invoices"

          DoCmd.TransferDatabase acImport, "Microsoft Access", TempDB, acTable, "Open Invoices", "Open Invoices"

          DoCmd.OpenQuery "Open Invoices - Trim Supplier Number"

          DoCmd.OpenQuery "Open Invoices - Trim Invoice Number"

          Set td = db.TableDefs("Open Invoices")

          Set fd = td.CreateField("Reconciled", dbText, 50)

          td.Fields.Append fd

          DoCmd.SetWarnings True

       

          DoCmd.Close acForm, "Processing", acSaveNo

          MsgBox "File Import Complete."

       

      Exit Sub

       

      Terminate:

       

          DoCmd.Close acForm, "Processing", acSaveNo

          MonarchObj.CloseAllDocuments

          MonarchObj.Exit

           

      End Sub[/CODE]

        • Upgraded to V9 - Automated export to Access broken
          RalphB _

          Hi Marcin and welcome to the forum.

           

          I run both Monarch Pro V8 and V9 with VB script and have no problem running either.

           

          I cannot see any problem with your script either.

           

          I'm wondering if one of the fields types have changed and is causing your problem. Whenever I have a problem exporting via VBA, I step through the script and stop when it comes to the export line and manually watch the export.  You can then see exactly what is causing the problem and correct the offending field/column.

           

          Try that and see what happens and let us know what happens.

           

          Ralph

            • Upgraded to V9 - Automated export to Access broken
              Grant Perkins

              Marcin,

               

              Not being a VBA user I may have got this wrong but as I read it your source file is a PDF, is that right?

               

              There have been regular changes to improve the spread of PDF interpretation functionality within Monarch and the PDF import options allow the selection of which import engine iteration to use.

               

              I'm just wondering if something in that area is leading to an anomaly in the eventual output that makes it mismatch the target as per RalphB's suggestion. Perhaps you have already checked that? Does the process run through if you follow it manually?

               

               

               

              Grant

                • Upgraded to V9 - Automated export to Access broken
                  Marcin _

                  There have been regular changes to improve the spread of PDF interpretation functionality within Monarch and the PDF import options allow the selection of which import engine iteration to use.

                   

                  I'm just wondering if something in that area is leading to an anomaly in the eventual output that makes it mismatch the target as per RalphB's suggestion. Perhaps you have already checked that? Does the process run through if you follow it manually?

                   

                   

                   

                  Grant[/QUOTE]

                   

                  Hi Grant,

                   

                  Actually, that seems to be the case.  Turns out I had the SetLogFile method on only the true side of a false if statement.  Once I sorted that out, it wrote the following log:

                   

                  Session started:    Monday, July 07, 2008 16:38

                  Set report file:    G:\Public\FSC-Payables\E08 - PAP & EDI820 Pymts\Input\Utility Invoices - 070108A.pdf

                  Failed while importing PDF file "G:\PUBLIC\FSC-PAYABLES\E08 - PAP & EDI820 PYMTS\INPUT\UTILITY INVOICES - 070108A.PDF".

                  The open PDF files were not completely re-imported after a change to the PDF import options, consequently all PDF files have been closed.

                  Set model file:     G:\Public\FSC-Payables\E08 - PAP & EDI820 Pymts\Open Payables.xmod

                  need to set report

                  Session ended:      Monday, July 07, 2008 16:39[/CODE]

                   

                  The odd part about it is that it doesn't seem to go into error until the model is opened.  I'm thinking that the model is forcing Monarch to import from PDF a second time and it is that second attempt that is failing.

                   

                  I will try to play around with PDF settings and report back on my findings. 

                   

                  Thanks,

                    • Upgraded to V9 - Automated export to Access broken
                      Marcin _

                      It turns out that Monarch V9 by default was using PDF import settings for 9.01 and my model was using PDF import settings for 8.01.  n00b me didn't know that models save PDF import settings.  Makes sense in retrospect.  Since the PDF import type was changing, Monarch forced a second import of the PDF text on model open.

                       

                      While the second import works just fine manually through the GUI, it fails when using automation.  Possibly a bug?

                       

                      Solution:

                       

                      Opened the report and model manually.  Changed PDF settings to use 9.01 and saved model.

                       

                      All is working as it should be. 

                       

                      Thanks everyone for your help with this!

                        • Upgraded to V9 - Automated export to Access broken
                          Grant Perkins

                          Marcin,

                           

                          Thanks for the detailed feedback. Very useful as a reference for all and it's good to have something that completes the record for those who will discover it in the future.

                           

                          I maybe should have been a bit more explicit about the PDF functionality in my earlier post but in my defence - I was guessing.

                           

                          That said you have run through some great analysis and I think we have probably all learned something new from it which is excellent.

                           

                          As you mention the behaviour is a little odd. Hopefully someone (not me for the reasons I mentioned previously) will come up with an explanation.

                           

                          Have fun.

                           

                           

                          Grant

                    • Upgraded to V9 - Automated export to Access broken
                      Marcin _

                      I'm wondering if one of the fields types have changed and is causing your problem. Whenever I have a problem exporting via VBA, I step through the script and stop when it comes to the export line and manually watch the export.  You can then see exactly what is causing the problem and correct the offending field/column.

                       

                      Try that and see what happens and let us know what happens.

                       

                      Ralph[/QUOTE]

                       

                      Hi Ralph,

                       

                      Good call.  Turns out that opening the model closes the report for some reason.  Since the SetReportFile method does actually open the PDF, it returns true.  So does the SetModelFile method.  Only problem is that the SetModelFile method closes the report and since there is nothing to export, the JetExportTable method returns false.

                       

                      Now I need to figure out why the model closes the report.  There is some sort of new feature in V9 that tracks project file relationships or something.  I wonder if that has anything to do with it.

                       

                      Any ideas?

                       

                      Thanks!

                       

                      P.S. Sorry for lateness of reply.  Been quite busy lately.  I appreciate the help from both of you.