How do you delete a recordset field?

[Deleted member 73419]

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:

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

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]

Hi. To maybe give us a chance to work through the logic, would you consider posting a cut down version of your db?

 

[Deleted member 73419]

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]

Hello,

Yes, of course. DB attached.

Hi. Thanks. When looking at it, I changed that line to this:

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

But it still produced a different error message.

 

[Galaxiom]

The DAO Recordset Fields Property is Read Only. The Delete Method of DAO Fields Collection applies only to TableDefs.

 

[MajP]

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]

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.

Faking a Multi Value Field

I have seen a few posts lately on people wanting to use multi valued fields. Most Access developers steer away from them and build their own related tables. Although they are properly normalized and work well, they can get very confusing even if you know the ins and outs. I wanted to see if I...

www.access-programmers.co.uk

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

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.

 

[Deleted member 73419]

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]

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!

 

[Isaac]

@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]

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.