How to run an embedded delete query in VBA

[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 !

 

[Brendan Reynolds]

CurrentDb.Execute "DELETE * FROM SomeTable WHERE SomeCriteria"

See 'Execute Method (DAO)' in the help files for more information.

 

[John Spencer]

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

 

[graeme34 via AccessMonster.com]

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 ???

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

[quoted text clipped - 4 lines]

performance....I think
Thank you !

 

[TC]

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

 

[Brendan Reynolds]

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

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 ???

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

[quoted text clipped - 4 lines]

performance....I think
Thank you !