3 Replies Latest reply: May 15, 2014 9:56 AM by Data Kruncher RSS

    MS Access import from Monarch V6.0

    osuapril _

      I'm using the code below to import into MS Access 2002.  I can get it to import the table but I want to import the summary.

       

      Currently using:

      On Error GoTo Err_cmd_JetExportTable_Click

       

      Dim monarchobj As Object

      Dim openfile As Boolean

      Dim openmod As Boolean

      Dim t As Boolean

      Dim expfile As Boolean

      Dim stringTableName As String

       

      MsgBox "You will now send data to the Table: AMR93002 in the ACH Rate Reduction Database."

       

      IsMonarchActive

       

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

       

      t = monarchobj.setlogfile("C:TempACHRateReduction.log", False)

       

      'open Monarch module

      'If openfile = True Then

      openmod = monarchobj.SetModelFile("filepathAMR93002.mod")

       

      'Append to Table (2= append, 1=Overwrite, 0=Create new )

      expfile = monarchobj.JetExportTable("filepathACHRateReduction.mdb", "AMR93002", 1)

       

      monarchobj.CloseAllDocuments

      monarchobj.Exit

      MsgBox "Your done."

       

      Exit_cmd_JetExportTable_Click:

      Exit Sub

       

      Err_cmd_JetExportTable_Click:

      MsgBox Err.Description

       

      I want to import the summary and overwrite the existing table.

        • MS Access import from Monarch V6.0
          Data Kruncher

          Hello and welcome!

           

          You're almost there.

           

          You'll need to replace this line:

          [font="courier"]expfile = monarchobj.JetExportTable("filepathACHRateReduction.mdb", "AMR93002", 1)  /font[/quote]with:

           

          [font="courier"]expfile = monarchobj.JetExportSummary("filepathACHRateReduction.mdb", "AMR93002", 1)  /font[/quote]Additionally, you should set the active summary just prior to executing the JetExportSummary.

           

          [font="courier"]monarchobj.CurrentSummary = "AMR93002"  /font[/quote]followed by a check to see if setting the current summary name actually worked (it exists in the model), just in case.

           

          [font="courier"]If monarchobj.CurrentSummary <> "AMR93002" Then

             'Error code goes here

          End If

            /font[/quote]As to replacing the code, obviously that's only relevant if you want to import the summary instead of the table, not in addition to the table.

           

          Is that what you had in mind?

           

          Kruncher

          • MS Access import from Monarch V6.0
            osuapril _

            I'm getting this error in my ErrorLog.

             

            Session started:    Tuesday, March 20, 2007 15:42

            Set model file:     Vs1-3415v-chf-s2chfshare2CHFSharedCMCSHAREHome Equity Projects Financial ProcessingMonarch ModelsAMR93002.mod

            The specified table already exists in the export file.

            Session ended:      Tuesday, March 20, 2007 15:42

             

            Code as is:

            On Error GoTo Err_cmd_JetExportTable_Click

             

            Dim monarchobj As Object

            Dim openfile As Boolean

            Dim openmod As Boolean

            Dim t As Boolean

            Dim expfile As Boolean

            Dim stringTableName As String

             

            MsgBox "You will now send data to the Table: AMR93002 in the ACH Rate Reduction Database."

             

            IsMonarchActive

             

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

             

            t = monarchobj.setlogfile("C:TempACHRateReduction.log", False)

             

            'open Monarch module

            'If openfile = True Then

            openmod = monarchobj.SetModelFile("filepathAMR93002.mod")

             

            'set Monarch filter

            monarchobj.CurrentFilter = "SID"

             

            'Append to Table

            expfile = monarchobj.JetExportSummary("filepathACHRateReduction.mdb", "AMR93002", 1)

             

            monarchobj.CurrentSummary = "AMR93002"

             

            If monarchobj.CurrentSummary <> "AMR93002" Then

            'Error code goes here

            End If

             

            monarchobj.CloseAllDocuments

            monarchobj.Exit

            MsgBox "You're done."

             

            Exit_cmd_JetExportTable_Click:

            Exit Sub

             

            Err_cmd_JetExportTable_Click:

            MsgBox Err.Description

            • MS Access import from Monarch V6.0
              Data Kruncher

              Did you want to add new data from the Summary to your Access table, or replace whatever is already there with new data?

               

              If you want to replace the data, change the 1 to a 0 in the JetExportSummary.

               

              Oh, and you should place the .CurrentSummary code before the .JetExportSummary call.

               

              If however you want to add new data to the existing data in the table, something unusal is happening, as the existing code should work as intended, and this situation will require further investigation.