Deleting a record, very slow.

[Dryseals]

I have a db with multiple users and multiple tables. One particular table is a bill of material. In the BOM table there is a BOM number, BOM item number and a part number. When the user needs to delete a part from the BOM, I run a simple query in code that seems to work but is at times extremely slow. The button to accomplish this is in tabular subform and there is one button for each record on the subform. The db is out on a local server with a access front end and the tables hidden in another directory. Click the button and this ciode runs, sometimes quick but other times very slow.
********************************
Dim dbs As Database, rst As Recordset

BN = Me.BOM_RCN
ITM = Me.BOM_ITM
Set dbs = CurrentDb
SSql = "select RCN, BOM_RCN, BOM_ITM from BOM where BOM_ITM = " & Me.BOM_ITM & " and BOM_RCN = " & Me.BOM_RCN
Set rst = dbs.OpenRecordset(SSql, dbOpenDynaset)
rst.Delete
rst.Close

*********************************
Is this the wrong way to do this? Is there a better way?

 

[PHV]

You may try this:
CurrentDB.Execute "delete from BOM where BOM_ITM = " & Me.BOM_ITM & " and BOM_RCN = " & Me.BOM_RCN


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mp9]

Try this (untested) 1-liner instead:

?dbengine(0)(0).Execute("DELETE * FROM BOM WHERE BOM_ITM = " & Me.BOM_ITM & " AND BOM_RCN = " & Me.BOM_RCN & ";")

http://www22.brinkster.com/accessory/