How to Pass a Table Name variable within a ODBC Connection?

Hi Al,

You can use the following code to pull data from a database table.  I tested this code with Monarch Classic v2023.1.  The code export data from table dbo.ClassicJan to an an Excel file (export2.xlsx).

 The odbc17-demodb in the connection string is an ODBC that i created on my server, which connects to a database named Demo.  The user id is demo.  

The dbo.Classjan is the table name within the database Demo.

You need to provide the password in the second argument where i put "your password here".

You also need to create a model in Monarch classic that basically pulls data from database table and displays it in Monarch Classic table view.  You can change the table name in the connection string as long as table columns matched in the model.

The ODBC that I created which connects to a Database required a password.  If you connect to a database using Windows authentication then you don't need the password.

You can click on the following link and review the Monarch Com Auto documentation:

https://help.altair.com/monarch/programmers_guide/altair_monarch_programmers_guide.pdf

 

Sub testDB()
        Dim monarchobj As Object

        Try
            monarchobj = CreateObject("monarch32")
            If monarchobj Is Nothing Then
                monarchobj = CreateObject("Monarch32")
            End If
            monarchobj.OPENDATABASE("Provider=MSDASQL.1;Persist Security Info=True;User ID=demo;Data Source=odbc17-demodb;Initial Catalog=Demo", "your password here", "dbo.Classjan", "C:\Datawatch\ComAuto\Demo_DB_with_odbc.dmod")
            monarchobj.ExportTable("c:\mo\export2.xlsx")

        Catch ex As Exception
            Console.WriteLine("Unexpected error: " & ex.Message)
        Finally
            monarchobj.CloseAllDocuments
            monarchobj.Exit
            monarchobj = Nothing
        End Try
    End Sub

Hi Mahmoud:

Many thanks... This almost got me there.  I cannot seem to get the Filter to work with the Monarch Model that is called.  See my VbScript, below.  Should the "CurrentFilter = NV_RECS" be inside the "OpenFile" string?

Sub testDB()

        Dim monarchobj As Object

' Try

            Set monarchobj = CreateObject("monarch32")

            If monarchobj Is Nothing Then

                monarchobj = CreateObject("Monarch32")

            End If

         monarchobj.CurrentFilter = NV_RECS       ‘FILTER NOT WORKING???

openfile = monarchobj.OpenDatabase("Provider=MSDASQL.1;Persist Security Info=True;User ID=financntr1;Data Source=BOOKPM;Initial Catalog= BOOKPM", "1financntr49", "DSAT0227PM", "C:\Users\rhoagland\Pitco Foods Dropbox\Rod Hoagland\New_Vena_Automation\Models\Masters_8X\90_NV_Inv_Eval_Test.dmod")

            monarchobj.ExportTable ("C:\Users\rhoagland\Pitco Foods Dropbox\Rod Hoagland\New_Vena_Automation\New_Master_8X\NV_ODBC_TEST.xlsx")

'Catch ex As Exception

'            Console.WriteLine ("Unexpected error: " & ex.Message)

'Finally

        monarchobj.CloseAllDocuments

        monarchobj.Exit

Set monarchobj = Nothing

'End Try

End Sub

Your help is very much appreciated.  You guys are the greatest!!

Thanks

Al

Hi Mahmoud:

Many thanks... This almost got me there.  I cannot seem to get the Filter to work with the Monarch Model that is called.  See my VbScript, below.  Should the "CurrentFilter = NV_RECS" be inside the "OpenFile" string?

Sub testDB()

        Dim monarchobj As Object

' Try

            Set monarchobj = CreateObject("monarch32")

            If monarchobj Is Nothing Then

                monarchobj = CreateObject("Monarch32")

            End If

         monarchobj.CurrentFilter = NV_RECS       ‘FILTER NOT WORKING???

openfile = monarchobj.OpenDatabase("Provider=MSDASQL.1;Persist Security Info=True;User ID=financntr1;Data Source=BOOKPM;Initial Catalog= BOOKPM", "1financntr49", "DSAT0227PM", "C:\Users\rhoagland\Pitco Foods Dropbox\Rod Hoagland\New_Vena_Automation\Models\Masters_8X\90_NV_Inv_Eval_Test.dmod")

            monarchobj.ExportTable ("C:\Users\rhoagland\Pitco Foods Dropbox\Rod Hoagland\New_Vena_Automation\New_Master_8X\NV_ODBC_TEST.xlsx")

'Catch ex As Exception

'            Console.WriteLine ("Unexpected error: " & ex.Message)

'Finally

        monarchobj.CloseAllDocuments

        monarchobj.Exit

Set monarchobj = Nothing

'End Try

End Sub

Your help is very much appreciated.  You guys are the greatest!!

Thanks

Al

Good Morning Al,

Please make sure you add the filter in the model that you are using in the script, and then add the Currentfilter method right before the ExportTable method.

Sub testDB()

        Dim monarchobj As Object

' Try

            Set monarchobj = CreateObject("monarch32")

            If monarchobj Is Nothing Then

                monarchobj = CreateObject("Monarch32")

            End If

         

openfile = monarchobj.OpenDatabase("Provider=MSDASQL.1;Persist Security Info=True;User ID=financntr1;Data Source=BOOKPM;Initial Catalog= BOOKPM", "1financntr49", "DSAT0227PM", "C:\Users\rhoagland\Pitco Foods Dropbox\Rod Hoagland\New_Vena_Automation\Models\Masters_8X\90_NV_Inv_Eval_Test.dmod")

             monarchobj.CurrentFilter = "NV_RECS"

            monarchobj.ExportTable ("C:\Users\rhoagland\Pitco Foods Dropbox\Rod Hoagland\New_Vena_Automation\New_Master_8X\NV_ODBC_TEST.xlsx")

'Catch ex As Exception

'            Console.WriteLine ("Unexpected error: " & ex.Message)

'Finally

        monarchobj.CloseAllDocuments

        monarchobj.Exit

Set monarchobj = Nothing

'End Try

End Sub

I tested my code and it worked:

If you have multiple filters you can add it before the exportTable method like the following:

            monarchobj.CurrentFilter = "CD"
            monarchobj.ExportTable("C:\mo\cd.xls")
            monarchobj.CurrentFilter = "dvd"
            monarchobj.ExportTable(C:\mo\dvdxls")

Regards

Mo

Brilliant!!!  Works like a charm.  

Many Thanks Mahmoud.

This matter is resolved.

Al