ansaurus

Question

How to delete a record and assign an existing record in its place.

Answer 1

+2 A:

Cascade update is a possibility, but probably overkill for something that will only happen occasionally and should not happen at all. I suggest that you run a query to update the relevant records before you delete the team leader:

Update Projects Set TeamLeaderID=123 
Where TeamLeaderID=456

Remou 2010-09-07 14:38:49

Think I got a different opinion about cascades (both update and delete). They seem like poking venomous snakes with your finger. If you're sufficiently skilled and attentive, you can escape harm. But even a momentary distraction ... you can get bit. So, if you cascade, please be careful. Always back up any data you want to keep.

HansUp 2010-09-07 15:49:56

I can't see how CASCADE UPDATE would do any good if the FK is an Autonumber. All that CASCADE UPDATE is update FKs when a FK value is updated, and since Autonumbers can't be updated, it would have no effect. Of course, if the PK of the record being deleted is not an Autonumber, CASCADE UPDATE would work, but you couldn't just change it to the value of the existing record you want to point the records to, as this would cause a collision on the PK's unique index. So, I just don't see what role CASCADE UPDATE could play in solving this problem.

David-W-Fenton 2010-09-07 22:09:41

The OP says 'and not allow duplicate entries' which suggests some design problems, which might make cascade update a possibility, however, you may notice that I said 'possibility', I did not say 'recommended'. I have found cascade update useful from time to time when cleaning data.

Remou 2010-09-07 22:28:14

I'm not disputing that sometimes it might be useful, but in this case I can't conceive of any way it would help address the question.

David-W-Fenton 2010-09-08 22:27:50

Cheers for the advice here folks appreciated.

glinch 2010-09-13 14:02:06

Answer 2

A:

Yes this is possible. It is a matter of knowing, where your worker is referenced, finding all such locations and replacing the reference through a new reference to another worker. Once all references to the old worker have been removed, you can delete the worker.

Option Compare Database

Private Sub test()
Dim db As Database
Dim rs As Recordset
Dim sql As String


Set db = CurrentDb
sql = "select worker_id from worker_ref_table"
Set rs = db.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges)

While Not rs.EOF
    rs.Edit
    rs!worker_id = "new value"
    rs.Update
    rs.MoveNext
Wend

rs.Close

sql = "select worker_id from worker_table"
Set rs = db.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges)

While Not rs.EOF
    rs.delete
Wend

rs.Close

End Sub

See also VB Database Programming for further help on connecting and operating on a database. Please note, that VB and VBA are not the same. You should be able to use the above code inside Access VBA to accomplish this task.

froeschli 2010-09-07 14:45:48

Why are you stepping through the tables when you can simply run an update query?

Remou 2010-09-07 15:11:55

I guess, once I learned something that works, but isn't quite the prime time way of doing things, I tend to stick to old ways. Using this method, I was able to update several fields on a remote sql server table. Might well be, that other methods are easier...

froeschli 2010-09-07 17:01:01

cheers for the technique froeschli

glinch 2010-09-13 14:03:08

ansaurus

tags:

views:

answers:

How to delete a record and assign an existing record in its place.

related questions