Questions about DAO recordsets, tabledefs, and forms

You've got a lot of information flying around there :)

A TableDef is a DAO object that represents a "table" of some sort (generally a database table). In general, you won't really use that to work with data, but instead you'd use that when you need to work with the "physical" table object (like if you you need to relink the tables, for example).
 
A DAO Recordset is a set of data which can be derived from the contents of a Table (a TableDef really has no bearing on a DAO Recordset).

<what relationship does the recordset returned by OpenRecordset have to the recordset it was called on>

I'm not sure I follow you. OpenRecordset "returns" a DAO.Recordset object. It's not dependent on another Recordset - it's built by DAO and is the return item from the OpenRecordset method. Your call here:

Set Me.Recordset = rstResults.OpenRecordset

Is basically saying "Open a Recordset named 'rstResults', and then set my form's Recordset to that Recordset". You could also do this:

Dim rstRestuls As DAO.Recordset
Set rstResults = DAO.OpenRecordset("SELECT * FROM SomeTable")
Set Me.Recordset = rstResults

Hey LSM,
 Thank you for the response. I know i do have a lot of information flying around. Sorry about that. As far as the Set me.recordset = rstResults call goes, that call fails. I get a runtime error saying that it's not a proper recordset object or something of that nature. 7965 was the error i believe. It only works if i call rstResults.OpenRecordset.

The thing that makes me think that recordsets are tightly linked to tabledefs is the fact that I can't just build a DAO recordset in memory apart from a tabledef. At least not as far as i can see. If I could, i would build a recordset, populate the rows with the parsing function and then set that recordset to the form's recordset independent of any tabledefs.

I would imagine that this is for the schema of the recordset. However, if i try to do anything to the base table that the recordset was built on then i get errors thrown at me. All recordsets based on the tabledef have to be closed.

Anyway,
 thank you again for your help. :)
Preston

According to documentation, DAO Recordsets "Represents a query result set with a cursor." A QueryDef "Represents a stored SQL query statement, with zero or more parameters, maintained in a Microsoft Jet database". Of course, QueryDefs are based on TableDefs, so there is certainly a relationship between a Recordset and a TableDef, but in general you'd think of a Recordset as a collection of Data, not something based on a TableDef.

DAO is very tightly coupled to the Access object model, which is why it's the preferred choice for Access development for most developers.

AFAIK, there is no method available to build an in-memory DAO Recordset as you describe. A DAO Recordset must always be built from a database table or query. You can use ADO for this sort of thing - build an in-memory Recordset, add Columns and such to it, then fill those columns. It's tedious but it can be done (see this link: http://www.databasejournal.com/features/msaccess/article.php/3846361/Create-In-Memory-ADO-Recordsets.htm)

<As far as the Set me.recordset = rstResults call goes, that call fails. I get a runtime error saying that it's not a proper recordset object or something of that nature. 7965 was the error i believe. It only works if i call rstResults.OpenRecordset.>

I've used the methods described by me to set Form recordsets but you must be careful how the recordset is built, and how it's managed. In most cases, unless you're working with remote data (and therefore need to use disconnected recordsets) there's really no benefit to building a Recordset and setting that as the Form's recordset. Just use direct SQL to do it:

Me.Recordsource = "SELECT * FROM SomeTable"

This will bind your form to that dataset.

If you can't do this with DAO, then please post the full code snippet where you're doing this (including declarations and such) and we can examine that to see if something is amiss.

<I would imagine that this is for the schema of the recordset. However, if i try to do anything to the base table that the recordset was built on then i get errors thrown at me. All recordsets based on the tabledef have to be closed.>

I'm not sure what you mean by this. A Recordset is just a representation of the data in a table. You can certainly alter a Table while using a Recordset, but again - we may be talking about two different scenarios. Again, if you'll provide more details (and code samples) we can review more thoroughly.

OK. Here is the code that i'm using.

In the form's module:
----------------------------------------------------

Private Sub cmdCheck_Click()
    Dim rst As DAO.Recordset
    Dim sql As String
    Dim rstParsed As DAO.Recordset
    
    If Not (IsNumeric(txtEnd) And IsNumeric(txtStart)) Then
        MsgBox "Enter only numbers for the ticket range"
        Exit Sub
    End If
    
    If CLng(txtEnd) < CLng(txtStart) Then
        Dim temp As Long
        temp = txtEnd
        txtEnd = txtStart
        txtStart = temp
    End If
    
    sql = "SELECT tblSOs.TechID, tblSOs.SvcOrder FROM tblSOs " & _
        "WHERE tblSOs.SvcOrder BETWEEN " & txtStart & " AND " & txtEnd
            
    'this recordset will give us a list of numbers and the techs
    'to whom the numbers have been assigned. we are getting the
    'parameters from two textboxes on the form
    Set rst = CurrentDb.OpenRecordset(sql)
    
    'if there are numbers in that range then they would
    'conflict with the assigning of new numbers to a new
    'tech
    If rst.RecordCount Then
        Label22.Caption = "Records overlap"
        'numbers have been found. let's parse out the numbers
        'in the range format (#-#, #, #-#, ...)
        'this is basically an aggregate function for the records
        'i do not know of a way to implement this in sql directly
        'using an inline function.
        Set rstParsed = parseNumbersRstTech(rst, 0, 1)
        rstParsed.MoveFirst
        Set Me.Form4.Form.Recordset = rstParsed
    Else
        Label22.Caption = "Free for use"
        Set Me.Form4.Form.Recordset = Nothing
    End If
    
    cmdCheck.SetFocus
End Sub

In the main module:
----------------------------------------------

Public Function parseNumbersRstTech(rst As DAO.Recordset, _
                techCol As Integer, numCol As Integer) As DAO.Recordset
    Dim rstOut As DAO.Recordset
    Dim records As String
    Dim cur As Long
    Dim st As Long
    Dim srt As String
    Dim tech As Long
    Dim tempTable As TableDef
    
    '========================================================
    'this function requires that a recordset be passed in containing
    'one column with techids and another column with the svcorders
    'as well as two zero based values indicating which columns contain
    'the required fields it will then return a recordset containing a
    'row for each tech and a string field with the parsed numbers
    '========================================================
    
    'get sort information to keep for replacing sort
    srt = rst.Sort
    rst.Sort = rst.Fields(techCol).Name & "," & rst.Fields(numCol).Name
    Set rst = rst.OpenRecordset
    
    'create temporary tabledef to return a recordset to the calling
    'function that holds the required information
    On Error Resume Next
        Set tempTable = CurrentDb.TableDefs("TemporaryTableDefForparseNumbersRstTech")
        If Err.number <> 0 Then
            Set tempTable = CurrentDb.CreateTableDef("TemporaryTableDefForparseNumbersRstTech")
            tempTable.Fields.Append tempTable.CreateField("TechID", dbLong)
            tempTable.Fields.Append tempTable.CreateField("SvcOrderRange", dbMemo)
            CurrentDb.TableDefs.Append tempTable
            Err.Clear
        Else
            DoCmd.SetWarnings False
                DoCmd.RunSQL "DELETE FROM TemporaryTableDefForparseNumbersRstTech"
            DoCmd.SetWarnings True
        End If
    On Error GoTo 0
       
    'open a recordset based on that table to return to the calling
    'function
    Set rstOut = CurrentDb.TableDefs("TemporaryTableDefForparseNumbersRstTech") _
        .OpenRecordset
    
    '#BEGIN Un-important stuff to parse out (aggregate) the records
    '============================================================
    If rst.RecordCount Then
        tech = rst(techCol)
        st = rst(numCol)
        cur = st
        rst.MoveNext
    
        While Not rst.EOF
            If tech = rst(techCol) Then
                If rst(numCol) <> cur Then
                    If rst(numCol) <> cur + 1 Then
                        If st = cur Then
                            records = records & st & ", "
                        Else
                            records = records & st & "-" & cur & ", "
                        End If
                        st = rst(numCol)
                        cur = rst(numCol)
                    Else
                        cur = rst(numCol)
                    End If
                End If
            Else
                If st = cur Then
                    records = records & st
                Else
                    records = records & st & "-" & cur
                End If
                rstOut.AddNew
                    rstOut(0) = tech
                    rstOut(1) = records
                rstOut.Update
                records = ""
                tech = rst(techCol)
                st = rst(numCol)
                cur = st
            End If
            rst.MoveNext
        Wend
        
        If st = cur Then
            records = records & st
        Else
            records = records & st & "-" & cur
        End If
        
        rstOut.AddNew
            rstOut(0) = tech
            rstOut(1) = records
        rstOut.Update
        
        If srt <> "" Then
            rst.Sort = srt
            Set rst = rst.OpenRecordset
        End If
        rstOut.MoveFirst
        '#END
        '============================================================
        
        
        'this line will fail. i cannot delete the tabledef that a
        'recordset was created from while the recordset exists
        'there is a definite relationship between the two
        'CurrentDb.TableDefs.Delete "TemporaryTableDefForparseNumbersRstTech"
        
        'if this assignment is not made with the .OpenRecordset function
        'then it will fail when the recordset is assigned in the calling
        'form's assignment: Set Me.subform.recordset = parseNumbersRstTech
        Set parseNumbersRstTech = rstOut.OpenRecordset
    End If
    
End Function

Select allOpen in new window

As I stated earlier, there is a fully defined relationship with a TABLE and a Recordset (a DAO Recordset is based on a Table or Query, and of course a Table/Query is based on a Table),  however I think we're mixing apples and oranges. A TableDef is the DAO representation of a Table, and is an object which allows you to work with a database Table. Deleting a TableDef is akin to deleting a Table, so of course you're not able to Delete a Table while it's tied to another object (a Recordset, Form, active Query, etc).

It seems you're dealing with a Subform, which is handled differently. If you need to set a Subform's Recordset, you do this:

Set Me.Subform.Form.Recordset = rst

The Me.Subform refers to the Subform CONTROL on the form, not the form being used as a Subform. It's somewhat of a subtle distinction, of course, but it is a distinction.

It seems to me that you could refactor this code to manage this differently, but I'm not entirely sure what this does. If you could explain, perhaps we could come up with a better way.

Finally: What's the need to delete your TableDef immediately? It makes no difference when it's removed, and in fact if you need this table on a regular basis, you might be better off building a permanant table and just filling it as needed.

======
As I stated earlier, there is a fully defined relationship with a TABLE and a Recordset (a DAO Recordset is based on a Table or Query, and of course a Table/Query is based on a Table),  however I think we're mixing apples and oranges. A TableDef is the DAO representation of a Table, and is an object which allows you to work with a database Table. Deleting a TableDef is akin to deleting a Table, so of course you're not able to Delete a Table while it's tied to another object (a Recordset, Form, active Query, etc).
=======

OK. I can understand this. I think the fact that when you try to delete a tableDef you are calling CurrentDb.TableDefs.Delete "" to delete it might be where I'm getting hung up. I am expecting a recordset to be independent of the tabledef once it has been populated. That must not be the model. I tried using SQL to open a recordset and then delete the tabledef it was called from and this failed as well.
e.g.
Set rst = Currentdb.OpenRecordset("SELECT * FROM Table1")

CurrentDb.TableDefs.Delete "Table1"  <---- Doesn't work until the recordset has been closed.
rst.Close



=========
It seems you're dealing with a Subform, which is handled differently. If you need to set a Subform's Recordset, you do this:Set Me.Subform.Form.Recordset = rstThe Me.Subform refers to the Subform CONTROL on the form, not the form being used as a Subform. It's somewhat of a subtle distinction, of course, but it is a distinction.
=========

The actual statement is below. I tried to shortcut and typed Me.subform. Sorry, i shouldn't have done that.
Set Me.Form4.Form.Recordset = rstParsed

========
It seems to me that you could refactor this code to manage this differently, but I'm not entirely sure what this does. If you could explain, perhaps we could come up with a better way.
========

This code is basically a custom aggregate query. It will take a recordset containing a group of technicians and the numbers they've been assigned
e.g.
Tech   Number
1         5678
1         5679
1         5680
4         5681
4         5683

and return the numbers Grouped By tech and the number range formatted as ##-##, #, ##-##, # etc...
eg.
Tech  NumberRange
1         5678 - 5680
4         5681, 5683

======
Finally: What's the need to delete your TableDef immediately? It makes no difference when it's removed, and in fact if you need this table on a regular basis, you might be better off building a permanant table and just filling it as needed.
======

There is no need to delete it immediately other than the fact that I was wanting to use this aggregating function as something that can be used from anywhere in the code. Having it not reside completely in memory will only limit me to record locks and things of that nature but that probably won't be an issue. I was trying to get creative with it and have it be reusable as well as encapsulated.

I appreciate you taking the time to try and walk me through this. I will most certainly award you the points I am just trying to gain some understanding.