12 Replies Latest reply: May 15, 2014 10:01 AM by Data Kruncher RSS

    Table Not Exporting

    rconverse _

      Hello,

       

      I am trying to duplicate a routine that I have working in another part of my Access database, but is not working in this sub.

       

      Any suggestions are appreciated.  I have been banging my head on this most of the afternoon.

       

      Basically, when I step through the code, I see everything happening except the export.  The database is created, but the table in the db is empty.

       

      Thanks,

      Roger

       

      strReport = "F:\OpenAccess\Daily Open PO\LUPRORTC"

      strModelName = "LUPRO_No_Filter.xmod"

      strPath = "F:\OpenAccess\Daily Open PO\"

      strExportTable = "Open PO Lines.mdb"

       

      Call Launch_Monarch(strReport, strModelName, strPath, strExportTable)

      /code

       

      Sub Launch_Monarch(strReport As String, strModelName As String, strPath As String, strExportTable As String)

      Dim strLog As String, strExpTable As String, strModel As String, blnCreated As Boolean

       

      strModel = "F:\OpenAccess\croy\Monarch Queries\" & strModelName

      strExpTable = strPath & strExportTable

      strLog = strPath & "Log.log"

       

      DoCmd.SetWarnings True

       

      'For testing

      'GoTo test

       

      'Delete Old Files

      On Error Resume Next

      'Kill strExpTable

      'On Error GoTo 0

       

          Dim MonarchObj As Object

          Dim openfile, openmod, t As Boolean

          '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 Err.Number <> 0 Then

       

                  blnCreated = True

                  Set MonarchObj = CreateObject("Monarch32") 'for late binding

       

          End If

       

          t = MonarchObj.SetLogFile(strLog, False)

          openfile = MonarchObj.SetReportFile(strReport, False)

          If openfile = False Then

                  openmod = MonarchObj.SetModelFile(strModel)

                  If openmod = True Then

                      With MonarchObj

                         .ExportTable (strExpTable)

                      End With

                  End If

          Else: openfile = True

              openmod = MonarchObj.SetModelFile(strModel)

                  If openmod = True Then

                      With MonarchObj

                         .ExportTable (strExpTable)

                      End With

                  End If

          End If

               

          MonarchObj.CloseAllDocuments

          MonarchObj.Exit

       

      'test:

       

      DoCmd.SetWarnings True

       

      End Sub

      /code

        • Table Not Exporting
          Data Kruncher

          Hello and welcome!

           

          Judging by your code, which largely seems functional, I'd say you already have the Programmers Manual PDF file.

           

          Have a look at the syntax of the JetExportTable method, and use it instead of the plain ExportTable.

           

          So long as you don't have filters which are excluding all records in your table, the export should be better with JetExportTable.

           

          Shout if you need futher help, and one of us will take it further with you.

           

          Kruncher

            • Table Not Exporting
              Nick Osdale-Popa

              Also, make sure that the .MDB file in question is not currently in use/open. Since you have On Error Resume Next[/b] you may not be seeing the File Open error being generated

                • Table Not Exporting
                  rconverse _

                  Also, make sure that the .MDB file in question is not currently in use/open. Since you have On Error Resume Next[/b] you may not be seeing the File Open error being generated[/QUOTE]

                   

                  Hello, and thank you for the reply.

                   

                  The db is not open when I run this.  When I don't use jetexport, the db is not created at all.  If I don't use jetexport, the db is created, but the table is empty.  When I do it manually, everything is fine.

                   

                  I'm about at my wits end!

                   

                  Thanks,

                  Roger

                    • Table Not Exporting
                      Nick Osdale-Popa

                      should strReport read as:

                      strReport = "F:\OpenAccess\Daily Open PO\LUPRORTC[B].PDF[/B]" ?

                        • Table Not Exporting
                          rconverse _

                          No.  This file is created from our reporting system.  Then it is FTP'd to our shared drive.  There is no file extension on the file.  When Monarch pulls up the file and applies the model, everything looks and feels like it is working exactly as intended.  Then, when the code hits the exporttable command, nothing is exported.

                           

                          Thanks,

                          Roger

                            • Table Not Exporting
                              Data Kruncher

                              Roger,

                               

                              Are you using v8 Standard, as your profile implies, or the Pro version? If it's the Standard version, you cannot use the JetExportTable method. Executing it won't do anything for you.

                               

                              I've run a little test of the ExportTable method, as I haven't used it in some time. It turns out that if you have a filter set on your table, the table that is created in your Access database is named after the active Table window filter. I'm not sure that this is the case in your model, but I'd watch for that. Also, I haven't tested a case where a filter is not active, or no filters are defined.

                                • Table Not Exporting
                                  Nick Osdale-Popa

                                  blnExpSuccess will be defaulted to FALSE when you DIM it.  That doesn't indicate that it's actually having a problem exporting the file. 

                                   

                                  When you are opening the file you still are trying to apply a model whether or not it was successful in opening that file. It may not be successfully opening it and be erroring out when it's applying the model. Since On Error Resume Next is invoked, every subsequent line still gets "executed" but the error already has occurred and the blnExpSuccess will fail anyhow.

                                   

                                  You may try to putting in a break point before it starts to open the report and step through the code to see where it's actually erroring out and check your variables. I'm thinking that openfile will be false thus everything else isn't executing correctly.

                                    • Table Not Exporting
                                      rconverse _

                                      I am on Monarch 8.0.  I am not on the pro version.  I see what you are saying about jetexporttable.  My initial attempt and one that worked in the past just used exporttable.

                                       

                                      I have run the code with errors set to true and get no errors.  I have also stepped through the code.  The file gets pulled into Monarch, the model does get applied.  I can see the records highlighted that I am trying to pull.  When it hits the export line of code, the code executes and the DB is created, but the table in the DB is empty.  I will check into the filters.  I do believe there is a filter created in this particular model, so I will look into if that is causing an issue.

                                       

                                      Thanks,

                                      Roger

                          • Table Not Exporting
                            rconverse _

                            Thank you for the reply.  I do have the pdf and have been reviewing it all today.  I did attempt the jetexport with the same results, unfortunately.

                             

                            strReport = "F:\OpenAccess\Daily Open PO\LUPRORTC"

                            strModelName = "LUPRO_No_Filter.xmod"

                            strPath = "F:\OpenAccess\Daily Open PO\"

                            strExportTable = "Open PO Lines.mdb"

                            strAccessTable = "Open PO Lines"

                            /code

                             

                            t = MonarchObj.SetLogFile(strLog, False)

                                openfile = MonarchObj.SetReportFile(strReport, False)

                                If openfile = False Then

                                        openmod = MonarchObj.SetModelFile(strModel)

                                        If openmod = True Then

                                            'With MonarchObj

                                               blnExpSuccess = MonarchObj.JetExportTable(strExportTable, strAccessTable, 0)

                                            'End With

                                        End If

                                Else: openfile = True

                                    openmod = MonarchObj.SetModelFile(strModel)

                                        If openmod = True Then

                                            'With MonarchObj

                                            blnExpSuccess = MonarchObj.JetExportTable(strExportTable, strAccessTable, 0)

                                            'MonarchObj.JetExportTable (strExpTable)

                                            'End With

                                        End If

                                End If

                                     

                                MonarchObj.CloseAllDocuments

                                MonarchObj.Exit

                             

                            If blnExpSuccess = True Then MsgBox "success"

                            If blnExpSuccess = False Then MsgBox "failure"

                            /code

                             

                            I did receive the failure message, so it's "working", but not working.  I have used this in a different routine and it worked fine.  I am not sure why I am having an issue now.  When I manually export, there is no problem.  Do I have to set monarch to table view prior to exporting or something?  That is the only thing that I can see that I am doing different when I manually export.

                             

                            Thanks,

                            Roger