Results 1 to 9 of 9
  1. #1
    Join Date
    May 2005
    Posts
    1,191

    Unanswered: Create DAO Recordset from Restrictred Fields of Form's Recordset

    Is it possible to set a recordset to only certain fields of a form's recordset? For instance, I can set a recordset to all fields of a form's recordset like:

    Dim rs as dao.recordset
    set rs = me.form.recordsetclone

    Can I limit the rs to only the 5th column of the form's recordset?
    Me.Geek = True

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Possibly yes, but how would you specify which columns have to be included in the new recordset?

    Have a nice day!

  3. #3
    Join Date
    May 2005
    Posts
    1,191
    Well, how could it be done with, say, only the fifth column? If I someone could tell me how to do this, then I think I can take that and apply it to my particular situation.
    Me.Geek = True

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    How about this?

    Code:
    Private Sub Command_Test_Click()
    
        Dim rst As DAO.Recordset
        Dim Field As DAO.Field
        Dim strFields As String
        Dim strSQL As String
        Dim i As Integer
        
        For Each Field In Me.Recordset.Fields
            With Field
                If .OrdinalPosition = 5 Then
                    If Len(strFields) Then strFields = strFields & ", "
                    strFields = strFields & .name
                End If
            End With
        Next Field
        strSQL = "SELECT " & strFields & " FROM (" & Me.RecordSource & ")"
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
        With rst
            Do Until .EOF
                For i = 0 To rst.Fields.Count - 1
                    Debug.Print rst.Fields(i).name, rst.Fields(i).Value
                Next i
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
    
    End Sub
    Have a nice day!

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    The problem is that the form is based off of a complex query that can take a while to run. So I don't want to have to run the query again with just a slimmed down Fields list; I just want to copy a single column from a recordset that has ALREADY been created and use that as my recordset (then I'll loop through and do stuff with it).
    Me.Geek = True

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Is there a reason why you can't use a clone of the original recordset and only use the column you need in your process?

    Have a nice day!

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    When I create a recordsetClone, it grabs all 50+ columns, and I only need one. So when I loop through the recordset it takes longer than necessary. So I'd like to either delete these unnecessary fields, or create the rs without them in the firstplace before I loop through.
    Me.Geek = True

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Moving the cursor on a recordset is not affected by the number of columns on the recordset. Are you sure you're addressing the correct problem?


    Why are you doing this? What symptoms have caused you to believe that creating a whole new recordset is going to give you a performance increase?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    May 2005
    Posts
    1,191
    Maybe if I try to give a little more explanation it might help: I have a form with a subform with a bunch of data in it (about 50 fields of 20k records or so). One of these fields is a memo field. I need to loop through all of these fields for all the records and concatenate them together. The query that populates this subform is based on a bunch of use-input and is fairly complex. So I'd like not to have to requery for just the one field, but rather use the recordset that has already been created.

    I can do this using the recordsetclone of the subform and looping through, the problem is when I do a rs.movenext in a "do while not rs.eof" type loop, it fetches all the fields for the next record, thus unnecessarily slowing down the looping process. So I'd like to simplify the recordset down to just the column I need before I start looping. How do I do this?
    Me.Geek = True

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •