• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 763
  • Last Modified:

Delete method raises error

I am trying to call the Delete method of the ADOB/Recordset object (ADO 2.1) to delete all the records in the table.
I have tried every CursorType / LockType combination and nothing works.  I have also tried changing the source property to "Select * From FTPFiles" to no avail.  I also get this error when I modify a record and call the Update method.

Depending on the CursorType / LockType combination I get one of the following errors

1. Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.
2. Operation is not allowed in this context.



            Dim rstFTPFiles As ADODB.Recordset
            Set rstFTPFiles = New ADODB.Recordset
            With rstFTPFiles
                Set .ActiveConnection = CurrentProject.Connection
                .Source = "FTPFiles"
                .CursorType = adOpenKeyset
                .LockType = adLockOptimistic
                .Open
            End With
       
            rstFTPFiles.Delete adAffectAllChapters    <--- Error occurs here
0
mmurphy1005
Asked:
mmurphy1005
  • 4
1 Solution
 
pique_techCommented:
I think you want the adAffectAll argument rather than adAffectAllChapters
0
 
mmurphy1005Author Commented:
I tried it.  I got the...

Operation is not allowed in this context.

Error.

0
 
pique_techCommented:
Sorry, that's not quite right either.  Should have tested.

I think to approch this this way, you'll have to do some kind of loop and delete the records one at a time.  Alternately, you could create a connection object and then EXECUTE "DELETE * FROM FTPFiles" against that connection.

Dim cn as ADODB.Connection
Set cn = currentproject.connection
cn.Execute("DELETE * FROM FTPFiles")
Set cn = Nothing

I actually tested this and it works perfectly.  : )

0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
pique_techCommented:
I think the recordset.delete method was designed to operate on a set of recordsets, the current record only, or a filtered recordset.  The Microsoft documentation (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthrstdelete.asp) is, surprisingly, not much help.  ; )
0
 
PlamodoCommented:
It wouldn't happen to be a query you are trying to delete records from, is it?  I know you mentioned 'table' in your initial question, but I thought I'd ask - since I've encountered error message #1 many times.
0
 
pique_techCommented:
mmrphy1005:  I'm curious what it was about my suggestions that warranted a C grade.  I'm not concerned about the points, I just want to be able to help others more effectively in the future.  Any feedback is appreciated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now