How to run an embedded delete query in VBA

  • Thread starter Thread starter graeme34 via AccessMonster.com
  • Start date Start date
G

graeme34 via AccessMonster.com

Hi I am fairly new to VBA/DAO/SQL, and I was wondering how to run an embedded
delete query in VBA, currently I have been using a strSQL = "SELECT query
with the WHERE criteria" to find the required record, opening a recordset
based on the strSQL then deleting the record....but surely the is a way of
doing this without resorting to opening a recordset...therefore improving
performance....I think :)
Thank you !
 
CurrentDb.Execute "DELETE * FROM SomeTable WHERE SomeCriteria"

See 'Execute Method (DAO)' in the help files for more information.
 
Assuming a known field and some string value identifying the field.

Public Sub sDeleteRecord()

Dim strSQL as String

On Error GOTO Handle_Error
'Build the sql to delete a record or records
StrSQL = "DELETE [TableName].[AnyFieldName]" & _
" FROM [TableName] " & _
" WHERE Somefield =" & Chr(34) & SomeCriteria & Chr(34)

CurrentDb().Execute strSQL, dbFailOnError

exit Sub

Handle_Error:
Msgbox err.number & ": " & err.description,,"Whoops!"

End Sub
 
Thank you both,.....one more question I also need to update a certain number
of records (approx 8 max), is it better (i.e faster) to use an update query
or create a recordset and loop through using .edit...... .update ???


Brendan said:
CurrentDb.Execute "DELETE * FROM SomeTable WHERE SomeCriteria"

See 'Execute Method (DAO)' in the help files for more information.
Hi I am fairly new to VBA/DAO/SQL, and I was wondering how to run an
embedded
Click to expand...
[quoted text clipped - 4 lines]
performance....I think :)
Thank you !
Click to expand...
Click to expand...
 
An update query is generally faster, if you have a large # of records.

Why not time the two methods yourself?

dim x a single
x = timer()
(do something)
x = timer() - x
msgbox x & " seconds!"

HTH,
TC (MVP Access)
http://tc2.atspace.com
 
In most circumstances, a query will usually be the most efficient way to
perform any kind of bulk change to records.

--
Brendan Reynolds
Access MVP


graeme34 via AccessMonster.com said:
Thank you both,.....one more question I also need to update a certain
number
of records (approx 8 max), is it better (i.e faster) to use an update
query
or create a recordset and loop through using .edit...... .update ???


Brendan said:
CurrentDb.Execute "DELETE * FROM SomeTable WHERE SomeCriteria"

See 'Execute Method (DAO)' in the help files for more information.
Hi I am fairly new to VBA/DAO/SQL, and I was wondering how to run an
embedded
Click to expand...
[quoted text clipped - 4 lines]
performance....I think :)
Thank you !
Click to expand...
Click to expand...
Click to expand...
 
Back
Top