none
using ado for treeview RRS feed

  • Question

  • Hello everyone.  Have an issue with filling a treeview from a sql back end table and access 2013 front end.  The tables are not linked.  The 'conn' is a public const that is used throughout the app.  So connection is not an issue.  Below is the vba code that I have sofar.  The error on form-load is:

    Error -2147217900 ([Microsoft][SQL Server Native Client 10.0][SQL Server]'BuildKey' is not a recognized built-in function name.) in procedure Form_Load....  BuildKey is in the strSQL statement.  I have included this at the bottom.

    BuildKey is a function that works if I link the Company table, remove Dim cmd and Dim rst, Set cmd, all the With cmd, and all the With rst.  The treeview fills with the structure without fail.  Unfortunately, the tables can not be linked so the ado needs to be there.  I have several other projects that used a treeview but those tables are linked (odbc).  This is mainly a copy of those but with using ado, no odbc.

    Would someone who does this take a look and see where the issue might be?  I'm at a loss.

    Thanks....John

        Dim cmd As ADODB.Command
        Dim rst As ADODB.Recordset
    
        Dim strSQL As String
        Dim tv As TreeView
    
        Set cmd = New ADODB.Command
    
        strSQL = "SELECT BuildKey(CompanyID) AS NodeKey, " & _
                    "Name AS NodeText, " & _
                    "BuildKey(Nz(ParentID,0)) AS Parent " & _
                "FROM dbo_tblCompany " & _
                "WHERE IsRemove = 0"
        
        With cmd
            .ActiveConnection = conn
            .CommandText = strSQL
            .CommandType = adCmdText
            Set rst = .Execute
        End With
        
        Set rst = New ADODB.Recordset
    
        With rst
            .CursorLocation = adUseClient
            .CursorType = adOpenStatic
            .LockType = adLockReadOnly
            .Open cmd
        End With
    
        Set tv = Me.tvwCompanyStructure.Object
        tv.Nodes.Clear
    
        AddNodes tv, rst
    
    Public Function BuildKey(ParamArray varKeyValues() As Variant) As String
    'Joins the values of the passed varKeyValues to build a key.
    'ALL Node keys MUST begin with a character.  For simplicity, this code
    'uses a constant, tv_conKeyDesignator, for this character.  Also, for
    'simplicity, all values used to build the key are separated by a constant,
    'tv_conKeySeparator.  This methodology is used in order to create a consistent
    'approach to building the nodes/keys on a tree view object.
    '
    'NOTE: In order to maintain consistency, it is recommended that you
    'set the PathSeparator property of the treeview object to be equivalent to
    'tv_conKeySeparator
    '
        Dim strTemp As String 'A 'working' string value
            
        'Build the key.
        strTemp = tv_conKeyDesignator & Join(varKeyValues(), tv_conKeySeparator)
        
        'Return the result
        BuildKey = Trim(strTemp)
        
    End Function
    


    Thursday, January 14, 2016 6:48 PM

Answers

  • You are passing the SQL statement to SQL Server to be executed, and SQL Server doesn't know what "BuildKey" is.  SQL Server doesn't have access to the VBA functions you've defined in your Access database.

    If you have design privileges on the SQL Server database, you could reproduce the logic of the VBA BuildKey function as a SQL Server scalar-valued function, and have the SQL statement call that function.  Or you could define the whole query as a SQL Server stored procedure that returns the properly constructed recordset.  Note that, if you want SQL Server to use the VBA constants you've declared, tv_conKeyDesignator and tv_conKeySeparator -- without defining them independently in the SQL Server database somewhere, then they would have to be parameters to the function or sproc, and you would have to modify what you pass via ADO to provide values for those parameters.

    An alternative to doing the design work in the SQL Server database would be to have the query just retrieve the raw data, and use your VBA Key function on the Access side as you process the returned recordset, to transform the raw CompanyID and Parent values as you need before adding the nodes.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by johnboy0276 Saturday, January 16, 2016 5:50 AM
    Thursday, January 14, 2016 7:12 PM
  • Looks like you are setting rst as recordset AFTER you populate the recordset instead of before.
    • Marked as answer by johnboy0276 Saturday, January 16, 2016 5:50 AM
    Thursday, January 14, 2016 7:51 PM

All replies

  • You are passing the SQL statement to SQL Server to be executed, and SQL Server doesn't know what "BuildKey" is.  SQL Server doesn't have access to the VBA functions you've defined in your Access database.

    If you have design privileges on the SQL Server database, you could reproduce the logic of the VBA BuildKey function as a SQL Server scalar-valued function, and have the SQL statement call that function.  Or you could define the whole query as a SQL Server stored procedure that returns the properly constructed recordset.  Note that, if you want SQL Server to use the VBA constants you've declared, tv_conKeyDesignator and tv_conKeySeparator -- without defining them independently in the SQL Server database somewhere, then they would have to be parameters to the function or sproc, and you would have to modify what you pass via ADO to provide values for those parameters.

    An alternative to doing the design work in the SQL Server database would be to have the query just retrieve the raw data, and use your VBA Key function on the Access side as you process the returned recordset, to transform the raw CompanyID and Parent values as you need before adding the nodes.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by johnboy0276 Saturday, January 16, 2016 5:50 AM
    Thursday, January 14, 2016 7:12 PM
  • Looks like you are setting rst as recordset AFTER you populate the recordset instead of before.
    • Marked as answer by johnboy0276 Saturday, January 16, 2016 5:50 AM
    Thursday, January 14, 2016 7:51 PM
  • Looks like you are setting rst as recordset AFTER you populate the recordset instead of before.

    Huh, I hadn't noticed that.  Good spot!  That will certainly not work, regardless of the issue with the VBA function.  The line:

        Set rst = New ADODB.Recordset

    should certainly not be there.  Either the whole "With rst" block after that statement shouldn't be there, or else the "Set rst = .Execute" line in the "With cmd" block shouldn't be there.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html


    • Edited by Dirk Goldgar Thursday, January 14, 2016 8:41 PM noticed more
    Thursday, January 14, 2016 8:38 PM
  • Mr Goldgar, the design privileges are a 'no' to do anything in the back end.  But your alternative sounds the way to go in this instance.  Although I am a bit lost in constucting the form_load event with the info above, so please bear with me.  Do you know of an example/sample that you can steer me to?  Otherwise, time to convince to link the tables.

    Also, thanks for the response.

    ...John

    Thursday, January 14, 2016 10:17 PM
  • No need for anyone to respond to the question above.  

    Thanks for stopping by.

    ...John

    Saturday, January 16, 2016 5:50 AM