I do know why ADO deletes records from both joined table
despite setting "Unique Table" property.
(I use ADO2.5 with Jet4.0 provider)

Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT Table1.KeyColumn1, Table2.KeyColumn1
FROM Table2 RIGHT JOIN Table1 ON Table2.Column2 = Table1.KeyColumn1"
rec.CursorLocation = adUseClient
rec.Properties("Unique Table") = "Table1"
rec.Open cmd, , adOpenKeyset, adLockBatchOptimistic

After selecting record and executing .Delete then .UpdateBatch
records from both tables get deleted.

What's wrong with this picture ?

--
Tom Jastrzebski

Please remove 'spam' on reply.

: I do know why ADO deletes records from both joined table
: despite setting "Unique Table" property.
: (I use ADO2.5 with Jet4.0 provider)
:
: Set cmd.ActiveConnection = cnn
: cmd.CommandType = adCmdText
: cmd.CommandText = "SELECT Table1.KeyColumn1, Table2.KeyColumn1
: FROM Table2 RIGHT JOIN Table1 ON Table2.Column2 = Table1.KeyColumn1"
: rec.CursorLocation = adUseClient
: rec.Properties("Unique Table") = "Table1"
: rec.Open cmd, , adOpenKeyset, adLockBatchOptimistic
:
: After selecting record and executing .Delete then .UpdateBatch
: records from both tables get deleted.
:
: What's wrong with this picture ?

Does your database have Cascade Delete on related fields set?

--

Hostes defutantur mei

[...]

Nope ...

I did not mention that my oryginal query is parametrized
and before deleting a record i usually call .Requery method
It looks like .Requerey is what causes the problem.

Just I still do know why.

Any way around it?
Thanks in advance.

Tom Jastrzebski

sample code:

cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
DBFile
cnn.Open
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT Table2.KeyColumn1, Table1.KeyColumn1  FROM Table1
LEFT JOIN Table2 ON  Table1.KeyColumn1 = Table2.Column2 WHERE Table1.Column2
= [id]"
cmd.Parameters.Append cmd.CreateParameter("id", adInteger, adParamInput)
cmd.Parameters("id").Value = Null
rec.CursorLocation = adUseClient
rec.Properties("Unique Table") = "Table1"
rec.Open cmd, , adOpenKeyset, adLockBatchOptimistic

Debug.Print rec.RecordCount
cmd.Parameters("id").Value = 1
rec.Requery
Debug.Print rec.RecordCount
rec.MoveFirst
rec.Delete
rec.UpdateBatch

I Downloaded a "HotFix" from the http://www.microsoft.com/data site (look a
little in there) that said it fixed KeySet cursor errors after doing a
delete. Apparently the recordset would return errors on Moving() the record
pointer after a delete. The HotFix, if I remember correctly was to ADO 2.1
Service Pack 2.

I'd use ADO 2.1 SP2, and the hot fix and try that.

Russ R.