.Delete Deletes From BOTH Joind Tables - Help! 
Author Message
 .Delete Deletes From BOTH Joind Tables - Help!

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.



Sun, 18 Aug 2002 03:00:00 GMT  
 .Delete Deletes From BOTH Joind Tables - Help!

: 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



Mon, 19 Aug 2002 03:00:00 GMT  
 .Delete Deletes From BOTH Joind Tables - Help!


[...]

Quote:
> Does your database have Cascade Delete on related fields set?

Nope ...


Mon, 19 Aug 2002 03:00:00 GMT  
 .Delete Deletes From BOTH Joind Tables - Help!


Fri, 19 Jun 1992 00:00:00 GMT  
 .Delete Deletes From BOTH Joind Tables - Help!
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



Mon, 19 Aug 2002 03:00:00 GMT  
 .Delete Deletes From BOTH Joind Tables - Help!
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.


Quote:
> 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



Tue, 20 Aug 2002 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Deleted records when text file is linked as table (#deleted)

2. SQL delete query deletes but does not delete

3. Help need to delete a Table

4. Delete Table Code Help

5. Help deleting/adding columns to MDB tables

6. DELETED tables cause error on table Connect?

7. deleting tables and updating table name

8. Retaining styles in table cells when deleting table

9. Deleting entry from table using ODBC - please help

10. Need help deleting Table

11. Help: Deleting Access Tables

12. DELETING TABLE, CREATING NEW TABLE

 

 
Powered by phpBB® Forum Software