Delete record in access database with excel VBA

Hi all, i have to delete some record from access database, at the moment i can insert and view data with this code:

For insert:
PercFile = ActiveWorkbook.Path _
& "\db1.mdb"
Set db = OpenDatabase(PercFile)
Set rs = db.OpenRecordset("Table", dbOpenTable)
Do While Cells(a ,b) > 0 then
With rs
.AddNew
.Fields("Data") = Cells(a, b)
.Update
End With
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing


For view:
PercFile = ActiveWorkbook.Path _
& "\db1.mdb"
conn.Open ConnectionString:= _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & PercFile
rst.Open "Table", conn, adOpenStatic
With rst
Do While Not .EOF
If rst.Fields("Number")=n Then
Cells(a , b) = .Fields("Name")
End If
.MoveNext
Loop
End With
conn.Close

i have try to modify the code that insert a record, with a .Delete that replace the .AddNew method but i obtain an error

Some idea?
thanks in advance

Hi Eureka,

.Delete method does work. But, you need to have selected a record first.

For example ...

Please Login or Register  to view this content.

While the code posted above will delete the first record, what you probably want to do is iterate through the recordset, find a record meeting a particular criterion, then delete that record.

Please Login or Register  to view this content.

If you have a very large table you are deleting data from, it would be MUCH faster to do this using a Delete query rather than iterating through one record at a time.

That would look like this:

strSQL = "DELETE Table.* FROM Table WHERE ((Table.MyField) = ""whatever"");"

db.Execute strSQL

="Excel" & "lent" thank you very much

Hi MSP77079, Would it be possible to show the SQL statement being executed in the context of a VBA sub. Not quite sure of the syntax i need to use to apply this to my own work. Like how to reference my particular table and fields etc.