How do you delete a recordset field? (1 Viewer)

cosmarchy

Registered User.
Local time
Yesterday, 22:32
Joined
Jan 19, 2010
Messages
77
I have the following code which, in simple terms, puts three RecordSets in to a dictionary (which allows me to use named RecordSets) where a function is called (Private Function RMDupes(ByRef RSS As Dictionary) and following that some processing takes place which then decides whether certain fields are required going forwards. If they are not I want to get delete the field in question:

Code:
Private Sub Command143_Click()
    
    Dim DB As DAO.Database
    Dim RS As DAO.Recordset
    Dim RSS As New Dictionary
    Dim Comp As New Dictionary
    Dim SQL As String
    Dim SQLB As String
    Dim ComparisonKey As Variant

    Set DB = CurrentDb
    
    SQLB = "SELECT [PN],[SU],[DE] FROM tbl WHERE ([PN] LIKE '')"
    
    Call Comp.Add("10001", "C1")
    Call Comp.Add("10002", "C2")
    Call Comp.Add("10003", "C3")
    
    For Each ComparisonKey In Comp.Keys
        SQL = Replace(SQLB, "''", "'" & ComparisonKey & "'", , , vbTextCompare)
        Set RS = DB.OpenRecordset(SQL)
        Call RSS.Add(Comp(ComparisonKey), RS)
        Set RS = Nothing
    Next
    
    Call RMDupes(RSS)
    
    '...follow on processing

End Sub

Private Function RMDupes(ByRef RSS As Dictionary)
    
    '...determine which fields need removing
    
    RSS.Items(1).Fields("SU").Delete 'HERE IS THE PROBLEM!!
    
 End Function

I don't want any copies of the RecordSets so am passing the dictionary across ByRef to ensure any action taken is on the actual data.

The error I am getting is
Screenshot 2021-09-21 211251.png

on this line RSS.Items(1).Fields("SU").Delete

This is clearly a simplified overview of the issue as I'm yet to determine the criteria for which fields to delete but the problem is really why I cannot delete a field and not which one!!

Does anyone know how to fix this?

Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:32
Joined
Oct 29, 2018
Messages
17,177
Hi. To maybe give us a chance to work through the logic, would you consider posting a cut down version of your db?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:32
Joined
Feb 19, 2002
Messages
33,777
I'm confused. If you don't need the field, don't include it when you build the structure. You could build code that opens a query or table in design view and deletes a column but you cannot perform any operation that affects the structure of something while it is open. A recordset is "open" That is what the "object doesn't support this method" error is telling you.
 

cosmarchy

Registered User.
Local time
Yesterday, 22:32
Joined
Jan 19, 2010
Messages
77
I'm confused. If you don't need the field, don't include it when you build the structure. You could build code that opens a query or table in design view and deletes a column but you cannot perform any operation that affects the structure of something while it is open. A recordset is "open" That is what the "object doesn't support this method" error is telling you.
Hi,
At the beginning all the fields are required until some pre-processing is carried out to determine which fields can be deleted. This is not known until the data has been processed and it may be that some fields in only certain RecordSets are required whilst others are not in other RecordSets.

The final result, or rather the contents of the RecordSets, will be displayed to the end user. Deleting the fields which are not necessary saves me having to filter out when it comes to displaying the data (not sure how you'd filter these out if I didn't delete them!!) but the ones which need to be deleted is down to the choices the user takes and / or the data in the rest of the RecordSet.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:32
Joined
Oct 29, 2018
Messages
17,177
Hello,

Yes, of course. DB attached.
Hi. Thanks. When looking at it, I changed that line to this:

Code:
RSS.Items(1).Fields.Delete "SU"


But it still produced a different error message.

1632261383880.png
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:32
Joined
Feb 19, 2002
Messages
33,777
1. Do the processing before building the recordset so you know what columns you need.
OR
2. Hide the columns you don't want to see.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 17:32
Joined
Jan 20, 2009
Messages
12,391
The DAO Recordset Fields Property is Read Only. The Delete Method of DAO Fields Collection applies only to TableDefs.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:32
Joined
May 21, 2018
Messages
5,690
Not sure the purpose of this. But if you store the original RS you know the fields collection. You know the RS SQL. If you identify which fields to delete you can very easily recreate the RS by keeping the fields in the select string that are not in the delete collection.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:32
Joined
May 21, 2018
Messages
5,690
Are your queries simple select queries as shown or are they complex queries? If they are simple doing what I suggested is doable. If this was complex then I would consider building an in memory ado recordset. You can loop the fields collection that you are keeping and create an in memory ado recordset with those matching fields and datatypes. Then you populate the new recordset based on the old recordset. Here is the most similar I have.
In this case I know ahead of time the fields I want, but I then read the DAO rs and populate the in memory ado recordset.
The relevant code
Code:
Public Sub CreateInMemoryRecordset(Domain As String, ValueField As String, Optional PK_Field As String = "None")
'Either Storing the PK or the value field in linked table
'Ensure reference to ADODB, Active Data objects
Dim rsFill As DAO.Recordset
Dim strSql As String

With InMemoryRS
   .Fields.Append "Selected", adBoolean
   .Fields.Append "ValueField", adVarChar, 255, adFldMayBeNull
   .CursorType = adOpenKeyset
   .CursorLocation = adUseClient
   .LockType = adLockPessimistic
   '.Open
End With

If Not PK_Field = "None" Then
   strSql = "SELECT " & PK_Field & ", " & ValueField & " from " & Domain
   InMemoryRS.Fields.Append "PK_Field", adBigInt ' currently only handle long PKs
Else
   strSql = "SELECT " & ValueField & " from " & Domain
End If
InMemoryRS.Open
Set rsFill = CurrentDb.OpenRecordset(strSql)
Do While Not rsFill.EOF
   InMemoryRS.AddNew
     InMemoryRS.Fields("ValueField") = rsFill.Fields(ValueField)
     If Not PK_Field = "None" Then InMemoryRS.Fields("PK_Field") = rsFill.Fields(PK_Field)
   InMemoryRS.Update
   rsFill.MoveNext
Loop

Me.txtValue.ControlSource = "ValueField"
Me.chkSelected.ControlSource = "Selected"
Set Me.Recordset = InMemoryRS
Me.Recordset.MoveFirst
End Sub

If you can hard wire which fields you will append then this should be very easy. You just need a bunch of if thens for each field in the RS.fields and not in the deleted dictionary. If you did this completely dynamically then you have to figure the DAO datatype and convert that to the correct ADO datatype constant. Just a big select case.
 
Last edited:

cosmarchy

Registered User.
Local time
Yesterday, 22:32
Joined
Jan 19, 2010
Messages
77
Ok, so this was an investigatory 'project' for a very simple comparison between multiple RecordSets.

The idea was to load multiple RecordSets and then compare the the same fields between the multiple RecordSets to see if the data is the same and then remove those particular fields as these contain little value in the sense of a comparison.

This is turning out to be a lot more involved since there are some rather simple and fundamental issues with Access / VBA / ADO etc which are making this more difficult than it really needs to be; not being able to delete a field being one of them!!
I'm sure there are very good reasons for this, so I'm not going to go in to this any further as I think I'll look towards a different way to achieve this using a different language or platform...

Thanks all those who've replied, much appreciated 😀😀
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:32
Joined
Oct 29, 2018
Messages
17,177
Ok, so this was an investigatory 'project' for a very simple comparison between multiple RecordSets.

The idea was to load multiple RecordSets and then compare the the same fields between the multiple RecordSets to see if the data is the same and then remove those particular fields as these contain little value in the sense of a comparison.

This is turning out to be a lot more involved since there are some rather simple and fundamental issues with Access / VBA / ADO etc which are making this more difficult than it really needs to be; not being able to delete a field being one of them!!
I'm sure there are very good reasons for this, so I'm not going to go in to this any further as I think I'll look towards a different way to achieve this using a different language or platform...

Thanks all those who've replied, much appreciated 😀😀
Good luck!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:32
Joined
Feb 19, 2002
Messages
33,777
not being able to delete a field being one of them!!
I explained WHY this is not possible earlier. You can't change the design of an object when it is open. I'm pretty sure you will find that a universal rule that applies to other application environments.

How are you displaying these recordsets once you determine what columns you want to show? If you bind the recordset to a form, all you need to do is to set the control's visible property to No. If the form is in DS view, the columns will compress. If the form is in single or continuous view, there will be a gap where the field was.
 

Isaac

Lifelong Learner
Local time
Yesterday, 22:32
Joined
Mar 14, 2017
Messages
5,924
@cosmarchy
Why not just store the information (anywhere - a table, a variable, a function return value), then run some DAO DDL to drop table columns as needed AFTER it's done?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:32
Joined
Feb 28, 2001
Messages
21,208
Or the simpler approach. Build SQL strings that include or exclude fields based on your findings. After all, a SELECT query that presents a recordset can be used in almost as many ways as a table-based recordset. And if you make the SQL string dynamically, you CAN drop fields.
 

Users who are viewing this thread

Top Bottom