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
mmurphy1005Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
pique_techConnect With a Mentor Commented:
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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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
All Courses

From novice to tech pro — start learning today.