Hi Chris,

Instead of opening the data set and finding/deleting each record that
matches, you can execute one SQL statement against the Connection object.
This will be faster and easier for you.

Here's an (untested) example:

Dim sSQL As String
Dim lRecordsDeleted As Long

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=G:\test.mdb"

sSQL="DELETE FROM TestTable WHERE [Month]='7'"

cn.Execute sSQL, lRecordsDeleted, adCmdText

MsgBox CStr(lRecordsDeleted) & " records were deleted."

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Chris Dunigan wrote:
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