Joined tables - deleting rows (ADO vs DAO) (1 Viewer)

[Martin99]

Not sure if this is a coding issue or a linking issue - it's a bit of both, but any ideas would be appreciated:

I have a stand-alone (C++ Builder 6) application which adds records to a "queue" table in a separate back-end MDB file using ADO. Then I have an Access 2K front-end user application which processes (and ultimately deletes) these records. At present this FE app uses DAO, and has worked fine for months at many sites.

However, just lately, a few sites are using more than one PC to run the front-end app, linked to the same back-end queue table to process it. At some (not all) of such sites, we have started to get file corruption of the back-end MDB queue file - "Unrecognised database format" errors. In some cases the corruption was fixable by opening and repairing the table in Access, in others not.

So my first question is this: any ideas why we might be getting this file corruption?

I've been reading around the subject of corruption, and I have done various checks of MDAC version levels etc. and I'm trying to piece together an evidence list to see what the common factors might be between the troublesome sites.

Another thought was that having DAO and ADO both accessing the same file in a multi-user way might be causing the problem, so I've been re-coding the FE app to use ADO instead. And this has led to another weird issue:

In the code I have a DAO recordset being opened on a query. The query left-joins two tables on just one field. At some point in the code, it calls Rs.Delete which deletes the record from the left hand table - which was the desired result. However, now that I have changed this to ADO, the call to Rs.Delete deletes the records from BOTH the tables in the query - which is not the desired result. This got me thinking about what I really would expect to happen when you delete a record from a query with a join in it, so I ran the same query directly in Access and then deleted a row from the query's form view, and (lo and behold) it only deletes the record from the left hand table. However, this is, of course, going to be using DAO, so I guess it's no real surprise.

Any ideas why ADO should be behaving differently? Or am I doing something wrong?

 

[Martin99]

Help! There is definately different behaviour between ADO and DAO here!

To prove the point to myself, I built a simple test using the same SQL to open the recordsets:


Option Compare Database
Option Explicit

' Using two simple tables (the fldID fields are irrelevant):
' tbl2.fldData2 is indexed/unique to make the query updateable

' tbl1:
' fldID fldData1
' ===== ========
' 10 Bananas
' 11 Oranges
' 12 Plums
' 13 Grapes
' 14 Pineapples
' 26 Pears

' tbl2:
' fldID fldData2 fldNotes
' ===== ======== ========
' 110 Bananas ABC
' 111 Oranges DEF
' 112 Plums GHI
' 113 Grapes etc
' 114 Pineapples etc
' 126 Pears ADO deletes this row, DAO doesn't!


Sub TestADO()

Dim Rs As New ADODB.Recordset
Dim sSQL As String

sSQL = "SELECT tbl1.fldData1, tbl2.fldData2, tbl2.fldNote " & _
"FROM tbl1 LEFT JOIN tbl2 ON tbl1.fldData1 = tbl2.fldData2 " & _
"WHERE (((tbl1.fldData1)='Pears'));"

Rs.Open sSQL, CurrentProject.Connection, adOpenKeyset, adLockPessimistic
Debug.Print "Rs recs: " & Rs.RecordCount

Rs.Delete
Rs.Close

End Sub


Sub TestDAO()

Dim Rs As DAO.Recordset
Dim sSQL As String

sSQL = "SELECT tbl1.fldData1, tbl2.fldData2, tbl2.fldNote " & _
"FROM tbl1 LEFT JOIN tbl2 ON tbl1.fldData1 = tbl2.fldData2 " & _
"WHERE (((tbl1.fldData1)='Pears'));"

Set Rs = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset)
Debug.Print "Rs recs: " & Rs.RecordCount

Rs.Delete
Rs.Close

End Sub

 

[Pat Hartman]

Do you have relationships defined between the two tables? Do you have RI enforced? Is the Cascade Delete option selected?

 

[Martin99]

Hi Pat,

No there are no relationships defined in the backend at all, in either case. In the test, I just created those two simple tables and ran the code. I'm baffled, largely because I can't believe I'm the first person to notice this behaviour. I'd be grateful if somebody else could reproduce it, just to be sure it's not something I'm doing!

Am I right in thinking the DAO behaviour is correct - only the rows from the left hand table SHOULD be deleted with this kind of join?

Thanks for the reply.

Martin.

 

[Pat Hartman]

Without a relationship defined, I'm not sure what the behaviour would be. I NEVER omit to enforce RI between related tables. I think your problem will be resolved if you create the relationship and enforce RI as you should. I'm guessing that DAO and ADO exhibit different behaviour when deleting a row from a query that joins two tables without a defined relationship. They are each making an assumption.

When the tables are related, a delete in a query that joins both tables will delete records ONLY from the many-side (or child in the case of a 1-1) table. If you want to delete the 1-side record AND you have cascade delete specified, the 1-side record will be deleted along with any related many-side records. If you have RI selected but not cascade delete, Access will reject the delete request if ANY many-side records exist since you cannot delete a parent without deleting its children.

So, if you want the correct results, change your query to select ONLY the correct records and enforce RI.

 

[Martin99]

In that case don't you find it astonishing that ADO goes ahead and deletes child records in the ABSENCE of anything specifically telling it to do so? This has to be an oversight, and one that really ought to be documented, given its potential for data destruction.

Surely ADO has all the information it needs from the LEFT JOIN clause in the SQL? I always thought that the whole power of SQL was to be able to tie together unrelated sources in this way and (if desired) modify those sources? I may not want (or even be able) to create the back-end relationships you are suggesting are necessary. For example, I may only be in control of the left-hand table, and simply be using some of the information in the right hand table to help me decide which of my records to delete.

This is where my current knowledge trails off, but presumably the "bottom line" of calling ADO's "Rs.Delete" results in some SQL that is sent to the connection for execution, and that in turn is processed by the Jet.OLEDB layer? It would be interesting to know what this SQL is and try running it on other connections.

 

[Summerwind]

As a slight aside, I'm wondering why you would use a recordset to delete records when using ADO.

Surely the better way to go would be to use a DELETE statement with an ADODB command? Firstly it would be quicker and secondly you have a much tighter control over exactly what is to be deleted.

 

[Martin99]

Any idea what the syntax of that DELETE command would be?

The reason I am using a Recordset in the "real" app is that the code operates on the record selected from the queue table by the SQL and when the user has finished working with the queued item, it needs to be deleted from the queue.

 

[Pat Hartman]

In that case don't you find it astonishing that ADO goes ahead and deletes child records in the ABSENCE of anything specifically telling it to do so? This has to be an oversight, and one that really ought to be documented, given its potential for data destruction.

Report it to Microsoft as a bug. I'm not sure the DAO solution is correct either. I would expect the many-side row to be deleted. You are saying that the 1-side row is deleted with DAO and both are deleted with ADO. Both is certainly wrong since you would need a relationship with cascade delete specified to delete from both tables.

 

[Pat Hartman]

If you can send me a database with both tables and the code, I will make sure that it gets to the correct people at Microsoft. I can't guarantee that they'll fix it though.