7 Replies Latest reply: May 15, 2014 10:12 AM by KeyserSoze _ RSS

    Multiple passes using JetExportTable

    mphillips@cvty.com _

      I'm cycling through multiple models on the same report and would like to export them to the same MS Access table. When I use MonarchObj.JetExportTable, it will create the table in the initial pass, but it will not append data from the subsequent passes. It does not throw an error.

       

      I am able to create a table for each pass and then combine them on the Access side but I'd like to eliminate the extra work by consolidating them during the Monarch stage.

       

      Here is the example of the code I'm using:

       

              Select Case pass

              Case 1

                  If openfile = True Then

                      openmod = MonarchObj.SetModelFile("model path")

                   

                      If openmod = True Then 

                          MonarchObj.JetExportTable ExprtPath, "FS-ConsolidatedTest", 1

                      End If

                  End If

              Case 2

                  If openfile = True Then

                      openmod = MonarchObj.SetModelFile("model path B")

                   

                      If openmod = True Then

                          MonarchObj.JetExportTable ExprtPath, "FS-ConsolidatedTest", 2

                      End If

                  End If

       

              etc....

       

       

      NOTE: the programmer's guide states: "Valid AppendFlag values are 0 for overwrite, 1 for new table or sheet and 2 for append to existing table or sheet."

        • Multiple passes using JetExportTable
          KeyserSoze _

          JetExportTable returns a Boolean value, so you should account for this in your code. Apart from that, and adding some parentheses, I don't see anything obviously incorrect.

           

          Per the manual, SetModelFile is meant to automatically close the current model file, so just using multiple SetModelFile calls shouldn't be a problem. That said, I do recall running into trouble when I didn't use CloseAllDocuments when preparing similar code.

           

          It adds a bit of processing time as you must re-open the data source (report file or what have you), but the code was rock solid stable when I used CloseAllDocuments before applying a new model.

           

           

           

          Select Case pass

          Case 1

             If openfile = True Then

                openmod = MonarchObj.SetModelFile("model path")

           

                If openmod = True Then

                   bSuccess = MonarchObj.JetExportTable(ExprtPath, "FS-ConsolidatedTest", 1)

                   If Not bSuccess Then

                      'failure code here

                   End If

                End If

             End If

          Case 2

             If openfile = True Then

                openmod = MonarchObj.SetModelFile("model path B")

           

                If openmod = True Then

                   bSuccess = MonarchObj.JetExportTable(ExprtPath, "FS-ConsolidatedTest", 2)

                   If Not bSuccess Then

                      'failure code here

                   End If

                End If

             End If[/CODE]

           

          Is this any better for you?

            • Multiple passes using JetExportTable
              mphillips@cvty.com _

              Even with the CloseAllDocuments, it trips and returns bSuccess = false.

               

              I'll try closing Monarch during each pass and see if that gets me anywhere.

               

              Also, is there a way to return an actual error message from Monarch? Something like MonarchObj.Err.Description

               

              Here's the code I'm using:

              For pass = 1 To 4

               

                      openfile = MonarchObj.SetReportFile("path" & TextFile, False)

                       

                      Select Case pass

                      Case 1

                          If openfile = True Then

                              openmod = MonarchObj.SetModelFile("model path")

                           

                              If openmod = True Then

                                       bSuccess = MonarchObj.JetExportTable(ExprtPath, "FS-ConsolidatedTest", 1)  '0 for overwrite, 1 for new table or sheet and 2 for append to existing table or sheet

                                       If Not bSuccess Then

                                          Debug.Print "1 failure code here"

                                       End If

                                   

                                  MonarchObj.CloseAllDocuments

                              End If

                          End If

                           

                      Case 2

                          If openfile = True Then

                              openmod = MonarchObj.SetModelFile("model path")

                           

                              If openmod = True Then

                                       bSuccess = MonarchObj.JetExportTable(ExprtPath, "FS-ConsolidatedTest", 2)   '0 for overwrite, 1 for new table or sheet and 2 for append to existing table or sheet

                                       If Not bSuccess Then

                                          Debug.Print "2 failure code here"

                                       End If

                                   

                                  MonarchObj.CloseAllDocuments

                              End If

                          End If

               

               

              /CODE

                • Multiple passes using JetExportTable
                  KeyserSoze _

                  Hmm...

                   

                  I wonder now if this is a programming problem or a model problem. Can you successfully append to the Access table using the second model manually (without using your program code)?

                   

                  Is the model used for the append the same as that used for the initial overwrite, or are you using a different model for the append?

                   

                  And are you writing to an MDB or ACCDB file? No, it shouldn't matter, but one never knows with these things.