Make Named Range from a sql query on a view

  • I'm running Excel vba 2016 code to make a Named Range from a query on SQL SERVER that is performed on a view. I have multiple tables in SQL SERVER that I'm doing this with. All of the ones that are run directly on a Table seem to work OK. I decided to make a few Views on data that would change more often thinking that the View would be updated when the data changed.


    I'm not sure this is what's causing the issue, but it's the only thing that has changed.


    Here are two subs that query the SQL SERVER, one for a table query the other for a View query.


    This works fine:

    Code
    1. Public Sub Package_Type()
    2. Dim SQL As String
    3. SQL = ""
    4. SQL = SQL & "SELECT DISTINCT [Package Type]"
    5. SQL = SQL & " From rdLab.tblPackage_Type"
    6. SQL = SQL & " WHERE (Package_Type_Is_Active = 1)"
    7. SQL = SQL & " ORDER BY [Package Type]"
    8. Call modDataValidation.GetDataFromSQL_SERVER("O2", "O", SQL, 12, "Package_Type", 1)
    9. End Sub


    This won't expand the Named Range:


    Code
    1. Public Sub Container()
    2. Dim SQL As String
    3. 'viewContainers is a view made from dbo.BomInfo
    4. SQL = ""
    5. SQL = SQL & "SELECT DISTINCT MATERIAL_COMPONENT, MATERIAL_DESCRIPTION_COMPONENT"
    6. SQL = SQL & " From dbo.viewCONTAINERS"
    7. SQL = SQL & " ORDER BY MATERIAL_COMPONENT"
    8. Call modDataValidation.GetDataFromSQL_SERVER("P2", "Q", SQL, 13, "Containers", 2)
    9. End Sub



    Here is the sub that runs the query and changes the Named Range:



    I'm not sure what the issue is. I have also tried first deleting the existing Named Range with no success.


    Thanks,

    Jeff