Maybe I made a mistake in terms of your system. However, I work a lot with
disconnected recordsets retrieved indirectly from a database via a web
server as an XML stream, and when using these the delete command works
perfectly well, with changes reflected in the db when the recordset is
passed back to the database via the web server with an updatebatch command.
I've had this working with both Access and SQL as the back end database
platform.

I would have thought that the fact there is a web server invovled is
irrelevant, but strangely I just tried to test it with a connected and a
disconnected recordset and Excel doesn't seem to like it. So, here's an
alternative solution. You effectively run an SQL command directly on the DB.
It's quite a neat trick, but be very careful to make sure you specify a
WHERE clause or you could rapidly delete all your data. Backup your db
before you try this please. Not perfect, but it should help. I think this
also answers the other question in Donnie's post.

Sub DeleteCommand()
'would take the same TSQL syntax as a normal SQL query
RunSQLCommand "DELETE FROM TestTable WHERE Month = '7'"
End Sub

Sub RunSQLCommand(strSQL As String)
Dim oConn As ADODB.Connection
Dim rsTemp As ADODB.Recordset

Set oConn = New ADODB.Connection
oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\My Documents\Temp\db1.mdb;Persist Security Info=False"

Set rsTemp = New ADODB.Recordset
oConn.Open
rsTemp.Open strSQL, oConn, adOpenStatic, adLockBatchOptimistic, adCmdText

Set rsTemp = Nothing
If oConn.State = adStateOpen Then oConn.Close
End Sub

Yours,

Robin Hammond
www.enhanceddatasystems.com

"onedaywhen" wrote in message
m...
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