Delete Queries

[dunnes]

Hi,
i have a list of Reference numbers in one table and i need to delete these ref no records from a different table, could anyone tell me the easiest way to do this. Havent used delete queries much

Cheers
Mike

 

[Tanis]

Try this SQL

DELETE YourTableName.*
FROM FieldName;

 

[mdmilner]

Try this

That's a little out of order.
Access doesn't do subqueries - so, you have to mix 'walking the recordset' with a deletion query with a parameter. You will need to change the relevant fieldnames to something useful but this will walk thru a recordset based on a SQL query returning a single value used as a Parameter in yours second query....a Deletion query.

Mike

Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strVal As String

Set dbs = CurrentDb()
strSQL = "SELECT * FROM tblName WHERE Field1 = 'condition' "
Set rs = dbs.OpenRecordSet(strSQL, dbOpenSnapshot

With rs
  Do Until rs.EOF
    strVal = .Fields(x).Value
    strSQL = "DELETE * FROM tblName2 WHERE FieldX = '"
    strSQL = strSQL & strVal & "'"
    DoCmd.RunSQL strSQL
    .MoveNext
  Loop
End With

Set rs = Nothing
Set dbs = Nothing