- Local time
- Yesterday, 18:41
- Joined
- Feb 28, 2001
- Messages
- 30,234
Scenario: I have a split FE/BE database, Access 2010 as the platform but using older (Ac 2003 .MDB) file formats for both FE and BE files. The tables visible to the FE are just "ordinary" linked tables from the Access BE file (i.e. this is not an SQL Server or mySQL BE).
I had been using DoCmd.RunSQL on some older code segments because of VBA things you can do with RunSQL vs. Execute - but now I'm trying to squeeze a little more speed out of this puppy so I'm pre-building the query strings for the subsequent Execute method. I didn't expect much - but I ran into something I didn't expect.
Let's consider a simple DELETE query:
sQry = "DELETE * FROM qtIAVFIL WHERE ActID = 1033 ;"
The string is built dynamically to contain the record number. It actually gets the value from a control on a form. But when the string is built, that is what it contains.
Query qtIAVFIL is a single-table query pointing to the back-end table tIAVFIL - I use queries this way because I found that if you use a query from the FE, you don't have to constantly supply a location for the table because the query knows where its table can be found. qtIAVFIL is NOT a JOIN query or a UNION query - or much else, for that matter. It is the moral equivalent of a "SELECT * FROM tIAVFIL;" except that instead of the *, it explicitly names the individual fields of tIAVFIL. In the underlying table, ActID is the PK.
Before I execute this DELETE query I enter the record with ActID 1033. I open the qtIAVFIL query to look at the 1033 record. It is definitely there (and happens to be the last record of the table). Then I do this:
Comparing the qtIAVFIL recordset before and after, the record with that ActID is present before I do the execute and is absent after I do the Execute, so that is good. BUT... the value of lRecs is 0 even though 1 record WAS deleted as confirmed by manual examination of query qtIAVFIL. Using DEBUG.PRINT from the immediate window also shows the CurrentDB.RecordsAffected to be 0
My alternative in the the past was to do DCOUNT functions before and after to verify that the DoCmd.RunSQL had done its job, but when I learned about the beneficial aspects of .Execute, I started conversion of my older code a little bit at a time. The .RecordsAffected property of CurrentDB looked ideal as a way to skip the extra DCOUNT functions - but if the property isn't being updated correctly, maybe that isn't such a benefit.
Has anyone else seen a problem in .RecordsAffected not returning a correct value? Is there something special I have to do to enable that property?
I had been using DoCmd.RunSQL on some older code segments because of VBA things you can do with RunSQL vs. Execute - but now I'm trying to squeeze a little more speed out of this puppy so I'm pre-building the query strings for the subsequent Execute method. I didn't expect much - but I ran into something I didn't expect.
Let's consider a simple DELETE query:
sQry = "DELETE * FROM qtIAVFIL WHERE ActID = 1033 ;"
The string is built dynamically to contain the record number. It actually gets the value from a control on a form. But when the string is built, that is what it contains.
Query qtIAVFIL is a single-table query pointing to the back-end table tIAVFIL - I use queries this way because I found that if you use a query from the FE, you don't have to constantly supply a location for the table because the query knows where its table can be found. qtIAVFIL is NOT a JOIN query or a UNION query - or much else, for that matter. It is the moral equivalent of a "SELECT * FROM tIAVFIL;" except that instead of the *, it explicitly names the individual fields of tIAVFIL. In the underlying table, ActID is the PK.
Before I execute this DELETE query I enter the record with ActID 1033. I open the qtIAVFIL query to look at the 1033 record. It is definitely there (and happens to be the last record of the table). Then I do this:
Code:
CurrentDB.Execute sQry, dbFailOnError
lRecs = CurrentDB.RecordsAffectedComparing the qtIAVFIL recordset before and after, the record with that ActID is present before I do the execute and is absent after I do the Execute, so that is good. BUT... the value of lRecs is 0 even though 1 record WAS deleted as confirmed by manual examination of query qtIAVFIL. Using DEBUG.PRINT from the immediate window also shows the CurrentDB.RecordsAffected to be 0
My alternative in the the past was to do DCOUNT functions before and after to verify that the DoCmd.RunSQL had done its job, but when I learned about the beneficial aspects of .Execute, I started conversion of my older code a little bit at a time. The .RecordsAffected property of CurrentDB looked ideal as a way to skip the extra DCOUNT functions - but if the property isn't being updated correctly, maybe that isn't such a benefit.
Has anyone else seen a problem in .RecordsAffected not returning a correct value? Is there something special I have to do to enable that property?
