CODEWITHSUNDEEP
sqlvbams-accessadodb
laurenz
laurenz

Reputation: 306

Really deleting entries of MS Access database with ADODB command

I would like to delete rows of an access table. Therefore I am using VBA with an ADODB command object.

The problem is that instead of really deleting the row, the query just sets all values except the ID to 0 / ""

What am I doing wrong or what do I need to do instead to really delete the entry?

Thank you in advance!

Code:

Dim db1 As ADODB.Connection
Dim db_data As ADODB.Recordset
Dim cmd As ADODB.Command

...'Code to build up Connection'

Set cmd = New ADODB.Command

cmd.ActiveConnection = db1
cmd.CommandText = "DELETE FROM TableXY WHERE FieldXY = ValueXY"
Set db_data = cmd.Execute

Upvotes: 0

Views: 2587

Answers (3)

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

You can simply do this without using the command:

Dim db1 As New ADODB.Connection
db1.ConnectionString = "your connection string here"
db1.Open
db1.Execute "DELETE FROM TableXY WHERE FieldXY = ValueXY"
db1.Close

Upvotes: 0

Gustav
Gustav

Reputation: 55816

ValueXY must have a value:

ValueXY = SomeValue
cmd.CommandText = "DELETE FROM TableXY WHERE FieldXY = " & ValueXY & ""

or, if text:

cmd.CommandText = "DELETE FROM TableXY WHERE FieldXY = '" & ValueXY & "'"

Upvotes: 0

Erik A
Erik A

Reputation: 32642

Try to explicitly delete all:

cmd.CommandText = "DELETE * FROM TableXY WHERE FieldXY = ValueXY"

You can also skip assigning it to a recordset: cmd.Execute instead of Set db_data = cmd.Execute.

Note that I can't replicate your issue.

Upvotes: 1

Related Questions