Neither the SQL DELETE keyword nor the recordset's Delete method is
supported for Excel. The nearest you can get is to SELECT..INTO a new
table/worksheet the rows/columns you want to keep and issue a DROP
TABLE for the original. DROP TABLE merely clears the sheet; the
worksheet is not removed from the workbook.

--

"Donnie Stone" wrote in message ...
Jake or Robin,

I posted a similar question a while back. How can I delete all records in a
table?

Thanks,
Donnie

"Robin Hammond" wrote in message
...
I don't know about other users but I have found that the filter command
does
not work reliably in Excel using the Jet provider.

You could try using the adaffectgroup option in your delete statement


http://msdn.microsoft.com/library/de...daenumac_5.asp

However, I'd suggest deleting the records one at a time. Unless you have a
huge amount of data it should still be quite quick.

Something like this (untested)

with rs
.movefirst
Do while not .eof
if .fields("Month").value = 7 then
.delete adaffectcurrent
else
.movenext
end if
loop
.updatebatch
end with

Robin Hammond
www.enhanceddatasystems.com


"Chris Dunigan" wrote in message
om...
Hi,

I would like to be able to delete multiple records in Access from a
procedure in Excel.

At the moment i have a macro set up in Excel that can look at my
database and tell me if there are any records in it that have "Month =
7". I'd like to have a pop-up box in Excel that then asks if the user
would like to delete all records from the database that match the
criteri "Month = 7".

My code looks like this:

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data
Source=G:\test.mdb"
rs.Open "TestTable", cn, adOpenKeyset, adLockOptimistic,
adCmdTable
rs.Find ("Month = '7'")
If rs.AbsolutePosition 0 Then
Test = MsgBox "TestMessage",vbYesNo, "Warning")
End If
If Test = vbYes Then
rs.Filter = "Month = '7'"
rs.Delete
rs.Close
End If

---------
At the moment this code does nearly all i want it to do, except when i
get to the rs.Delete line. It only deletes one record, and not all
the records that have been found using the rs.Filter line.

Can anyone tell me how i can get the macro to delete every record
brought back from the rs.Filter line.

Many thanks in advance

Chris