ADO and DAO delete behaves diferrently on joined tables?

[Martin88]

I am changing an Access 2K app from DAO to ADO (to try to resolve data corruption issues) and I seem to be getting different behaviour from the .Delete methods when they are used on a recordset involving two left-joined tables.

The DAO version deletes rows from the left hand table only, but the ADO version deleted rows from BOTH tables.

To prove the point to myself, I built a simple test using the same SQL to open the recordsets (see code below).

I can't believe I'm the first person to notice this behaviour, so 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?



Code:

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,fldNote
' 110,Bananas,ABC
' 111,Oranges,DEF
' 112,Plums,GHI
' 113,Grapes,etc
' 114,Pineapples,etc
' 126,Pears,THE ADO CODE DELETES THIS ROW BUT THE DAO CODE DOES NOT !!!!!


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

Sub TestADO()

Dim Rs As New ADODB.Recordset

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

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

End Sub

 

[Martin88]

A worrying lack of replies! Has nobody ever seen this behaviour before?

 

[cmmrfrds]

After a quick glance, it is probably the difference in where the cursor is setting. My guess is that ADO positions the cursor at the last record and DAO does not position the cursor until you do something with it. In either case, the cursor should be positioned where you want it before deleting.

 

[Martin88]

Thanks for the suggestion cmmrfrds, but I am positioning the cursor in my "real" app. To prove the point, I put a

Rs.MoveFirst

above each of the "Rs.Delete" lines in the test app above, and the behaviour is just the same - the ADO code deletes the record in the right hand table and I'm sure it shouldn't!

 

[cmmrfrds]

I don't use the Delete method of the recordset so have not had the problem. Bottom line is that ADO by default will delete both sides. You need to set the unique table property. The following 2 articles should explain all. Make sure you use a client side cursor to make the unique table property available.

See these articles.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprodynuniquetable.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprodynuniquetable.asp