none
query a recordset on a form_load RRS feed

  • Question

  • Hello.  Have sql server back end, ms access front end, using ado, tables are not linked (long story).  In the form load of a form I have a sql statement that fills a treeview, if the table is linked.  I need to establish a recordset first and draw info from there with the SELECT statement that fills the treeview.  But the treeview SELECT has a FROM and that is where I am getting dinged.  With using a recordset as the place the treeview is getting its info from what do I put as the FROM?  Tried the recordset name as well as others but to no avail.  Even did a create view and that worked the first time thru then it failed and also created a view in sql server.  The dba's did not like that.  Any suggestions?

    Thanks....John

    Sunday, January 17, 2016 9:16 PM

All replies

  • Hi John,

    Just a stab in the dark since I think a VIEW is more appropriate but seems to be not allowed in your case, have you tried using either a query or a temporary table for your treeview?

    Just my 2 cents...

    Sunday, January 17, 2016 9:29 PM
  • A temp table.  DOHHHH.  I can't believe I never thought about it.  I believe a temp table would suffice and then at the end of the form load delete (or drop) it.  If only it stays in Access and doesn't get back to SQL Server.  The view freaked me out at first when I saw it in Access under the queries.  I deleted it but kept getting notice of it being there.  Went to ssms and oh oh there it is under the view folder of the db.  Yeah.  That deserved a walk to the server room rather that a call over the phone to let them know about it.  Getting back to the tmp table, I have very little experience with that.  Below is the vba that I have so far in the form load.  The sqlCompany is where I had the Create View in front of the Select.  I would want to delete the tmp table, that is my question.  Placed at the bottom and drop the tmp table is done how?

    Oh, and thanks for the reply on this.

    ...John

        Dim strSQL As String
        Dim tv As TreeView
        
        Dim rstCompany As ADODB.Recordset
        Dim sqlCompany As String
        Set rstCompany = New ADODB.Recordset
        sqlCompany = "SELECT * FROM tblCompany"
        rstCompany.Open sqlCompany, conn
        
        'building the treeview
        'this goes in front of "Name AS ..." >>> Right(NodeKey, Len(NodeKey)-2) + "" "" +
        'by adding the above puts the CompanyID in front of name in the treeview
        strSQL = "SELECT BuildKey(CompanyID) AS NodeKey, " & _
                    "Name AS NodeText, " & _
                    "BuildKey(ParentID) AS Parent " & _
                "FROM vwHierarchy " & _
                "WHERE IsRemove = 0"
        
        Set tv = Me.tvwCompanyStructure.Object
        tv.Nodes.Clear
    
        AddNodes tv, strSQL
        btnExpand_Click
         
        rstCompany.Close
        Set rstCompany = Nothing
    

    Sunday, January 17, 2016 10:03 PM
  • Hi,

    I don't use ADO much but see if something like this will work:

    sqlCompany = "SELECT * INTO tmpCompany FROM tblCompany"
    
    CurrentProject.Connection.Execute sqlCompany
    
    

    Hope that helps...

    Sunday, January 17, 2016 10:33 PM
  • Hi, johnboy0276

    According to your description, I suggest that you could refer to this helpful link about How to fill a Treeview control recursively in Access 2000

    Monday, January 18, 2016 3:00 AM
  • Thanks.  Actually, the filling of the treeview is not the issue.  Unless I missed something in the link.  As you can see from the code in above post, it is getting the info from the db with ADO.  If I link the tblCompany table, the form loads and treeview displays the info without issue.  But, if the table is not linked and ADO is strictly used, the strSQL does not display the hierarchy correctly.  What was determined is getting the table into the process and have the treeview strSQL draw from that information.  I considered a recordset, a view, and a temp table but the latter two both showed up in the sql server database.  Glad the db is not in production, yet.  So, that is the whole issue in a nutshell.  Need to get the tblCompany info into the form load so the strSQL can draw from it and then the initial temp table, recordset, or view all goes away without leaving a trace.  I know that the windows form and C# can do this rather simplely.  But this is Access and vba.
    Monday, January 18, 2016 4:19 AM
  • >>>Need to get the tblCompany info into the form load so the strSQL can draw from it and then the initial temp table, recordset, or view all goes away without leaving a trace.  I know that the

    windows form and C# can do this rather simplely.  But this is Access and vba.<<<

    According to your description, if I don't misunderstand, you maybe want to use Filter property on a Recordset object indicates a filter for data in a Recordset.

    In addition you could refer about How To Work w/ Microsoft Access QueryDef Parameter Using VB

    Tuesday, January 19, 2016 9:17 AM