Good evening group, a new question for my project. Always in my access database, I delete records with: db.ExecuteSQL( "DELETE FROM " + NameOfMytable … etc…etc…
Is there a way to count the deleted records?
I’m not familiar with Access. Does it have a RETURNING keyword?
I’m sorry, I don’t know what to tell you, I don’t know RETURNING
Some SQL variants, like PostgreSQL, let you tack a RETURNING clause onto a command like INSERT or DELETE. In your case, if Access allows it, you can return the deleted records and count them.
Otherwise, others might have a better idea, but in a transaction I’d SELECT COUNT(*) first, then delete, then return the count.
1 Like
Microsoft recommends, first count based on the select, then delete. There seems no RETURNING.
1 Like
Or, you can design your database so that nothing ever gets truly removed from the DB. We will sometimes add a IsDeleted column in our table and set it true once it’s ‘deleted’. Obviously we have to modify all queries that only want non-deleted records to be queried as such.
We also have a lastModifiedTime and lastModifiedUser so you can see who was the last person to touch it. But you’re getting into some advanced database techniques.
6 Likes
Most DB drivers have a thing to get a number like RecordsAffected() by the last operation, by seem Xojo hasn’t.
Access do what in Xojo would be like:
db.Connect()
db.ExecuteSQL("DELETE FROM my_table mt WHERE mt.erase = ""OK"";")
Var NumberOfDeleteds As Integer = db.RecordsAffected()
We had something like that in Xojo in the past or am I mixing memories from other environments?
1 Like
No, there is none*. Only by counting what you are about to delete before you actually delete it. 
Edit: *Already posted by @Thomas_Kaltschmidt 
I see Microsoft is ignorant about English. From their website:
criteria An expression that determines which records to delete.
They seem to be unaware that:
criterion - is singular
criteria - is plural
Hey, it still there, but just for some DBs?
I searched and found:
two access there: one before delete, one after and simple “-” math and you get it.
But Rick answer seems better.
seems odbc database class in xojo does not have .AffectedRowCount
and i don’t know if ODBC support RETURNING
i would try
var result as RowSet
result =db.SelectSQL("DELETE FROM table WHERE xy returning SQLRowCount")
or you use a transaction where you count first and delete.
that would useful for ask the use if he is sure to delete xxxxx records.
I think the underlying database has to support RETURNING. SQLite and Postgres (?) do. But the item after RETURNING is an expression, rather than a keyword.
I started using RETURNING after it became clear that LASTROWID would be deprecated.
Wait. What? Where did you see this?
From the 2024r2 RelaseNotes:
Added an overloaded Database.AddRow(tableName As String, row As DatabaseRow, idColumnName As String = ) As Integer This returns a unique id of the newly inserted row. This API should be preferred over using the more specific SQLiteDatabase.LastRowID and MySQLCommunityServer.LastInsertedRowID which are now deprecated.
Ah. Thanks. Since the likelihood of us ever using API2 is slim I can see why I missed this.
1 Like
I see they removed LastRowID from the documentation, but did not supply a replacement for the people using ExecuteSQL and not AddRow. It’s almost as if the person directing these changes doesn’t actually write software.
5 Likes
Ahh, this is no problem, you can do an
SELECT `id` FROM `table` ODER BY `id` DESC LIMIT 1
after your insert. No problem…
If I say anything about it, I’m just the bad guy again. I grin and abstain.
in a multi user enviroment we must be more careful.
1 Like
That’s the problem with irony and sarcasm
1 Like