Solved

Questions about DAO recordsets, tabledefs, and forms

Posted on 2010-11-10
7
850 Views
Last Modified: 2012-05-10
Hi,
 I am trying to get a grasp on what is going on with the code that I'm using to set a form's recordset property.
I have a form that has 2 text boxes. I am using these text boxes to get a start and end value and search for everything in between. (just numbers)
Here is how the code progresses:
1. The code runs a query and checks if any numbers exist between the two numbers given

2. If numbers exists it calls a function and passes the recordset to it for parsing.

3. The function will create another recordset based on a tabledef that has a line for each employee and the numbers they've been assigned. the numbers get formatted as ## - ##, ##, ##, ## - ## so that they are not all displayed

4. The function then returns its value as FUNCTIONNAME = rstResults.OpenRecordset

5. This recordset returned is then set to a subform's recordset property and displays the results

This does not seem to work without using Set form.recordset = recordResults.OpenRecordset. If i call it directly without OpenRecordset i get a runtime error 7965. Why is this?
Also, what relationship does the recordset returned by OpenRecordset have to the recordset it was called on? What relationship does the recordset returned by OpenRecordset have to the tabledef that the initial recordset was built on? What relationship does the initial recordset have to the tabledef it was built on.

Sorry to seem like i'm spamming questions but they are all trying to shed light on the same thing. What relationship does a DAO recordset have to a tabledef it was built from? I think there is some gaps in my understanding of this.
Thank you for any help and if you need clarification then I can try to explain better what I'm after.
Thank you,
 Preston
0
Comment
Question by:prophet001
  • 4
  • 3
7 Comments
 
LVL 84
ID: 34105452
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


0
 

Author Comment

by:prophet001
ID: 34105565
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
0
 
LVL 84
ID: 34110288
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.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:prophet001
ID: 34111517
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

Open in new window

0
 
LVL 84
ID: 34112686
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.


0
 

Author Comment

by:prophet001
ID: 34113128
======
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.


0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 34116050
Thanks for the clarifications.

Using a temporary table wouldn't really cause record locks, unless you're referring to locks on the temp table. If so, you could always resort to Class modules with data structures, but that can get unwieldy fairly quickly. Arrays are another option and might be a better fit.

0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now