Changing Relationships Programmatically (2002)

Topic

New Reply

Hi, it’s your backward student again.
My latest assignment is to ” Delete all of the data in the _________ table.”
Well — I can’t do that because the table is in a relationship with other tables.
Can someone give me a hint on where to even start looking for a way to nullify the relationships so I can delete the records?

Reply | Quote

Replies

Although it is possible to change relationships in code, you must ask yourself if it is desirable. Let’s say that Table A has a primary key that is related to a foreign key in Table B. If you were to delete the relationship, then delete all records in Table A, the records in Table B would become orphaned: they have a foreign key that does not refere to any existing record in Table A. In most real-life situations that is undesirable.
One option is to first delete all records in Table B, and in all other tables with a foreign key that refers to the primary key in Table A. Only then delete the records in Table A.
Another option is to set Cascading Deletes for the relationships joining Table A to other tables. When you delete records from Table A, related records in other tables will automatically be deleted too.
If you need to manipulate relationships in code, the easiest way is to set a reference to the Microsoft DAO 3.6 Object Library, and work with the CurrentDb.Relations collection.

Reply | Quote

Hans darlin’
We aren’t talking about the real world. This is a class assignment. It is MOST desirable for me to be able to complete it. I have the code that will delete the table, and the code that would delete all records, but they fail because of the relationship, I really really need to break the relationship. Your absolutly correct – Microsoft DAO library and the CurrentDb is exactly what I’m using. — Wait a minute. You said CurrentDb Relations collection. — I’m off to research, Thank you.

Reply | Quote

If this is for a class, isn’t asking for answer online cheatin’?? (joke)

Anyway for example of code to delete relationship(s) for a specified table, see attached text file. The DeleteTable sub tries to delete table, if Error 3281 results, table cannot be deleted because is used in relationship. In this event DeleteRelation sub called from error handler:

Public Sub DeleteRelation(ByRef TableName As String)
On Error GoTo Err_Handler

Dim db As DAO.Database
Dim n As Long
Dim i As Integer
Dim strMsg As String

Set db = CurrentDb

' Relations collection is zero-based
For n = db.Relations.Count - 1 To 0 Step -1
If db.Relations(n).Table = TableName Or _
db.Relations(n).ForeignTable = TableName Then
db.Relations.Delete db.Relations(n).Name
i = i + 1
End If
Next n

' Test msg:
If i > 0 Then
MsgBox i & " relations for " & TableName & " table have been deleted.", _
vbInformation, "RELATIONS DELETED"
Else
MsgBox "No relations found for this table (" & TableName & ").", vbInformation, "NO RELATIONS FOUND"
End If

Exit_Sub:
Set db = Nothing
Exit Sub
Err_Handler:
strMsg = "Error No " & Err.Number & ": " & Err.Description
MsgBox strMsg, vbExclamation, "DELETE RELATION ERROR MSG"
Debug.Print strMsg
Resume Exit_Sub

End Sub

This code deletes any relationship where table is involved as either primary or “foreign” table in relationship, as indicated by Relation object Table and ForeignTable properties. Run EnumRelations sub to list all relations in current db and table names (note Relation name is usually a composite of the names of two tables involved, but not always, so do not recommend use Relation name as basis for deletion). Note also, for this type of thing, step thru the collection from last item to first, or else things will get out of whack when you delete object & the remaining objects are re-indexed.

HTH

Reply | Quote

Ordinarily I would agree with this. Which is why I only asked for a hint. However, for this class — There are no lectures, there are no other students, the instructor responds little or not at all, (online class). Not all of the information needed is included in the assigned or previously assigned material. We are apparently supposed to research it and figure it out.
Besides, my friend, who is a supervisor at Microsoft (really), told me that this is the way programmers work in real life. They do what they know how to do, hit the books, and if they can’t find it there, ask other programmers. So I am not only learning VBA, I’m learning to work like a real programmer/
I’m apparently learning a little advanced rationalization as well.
Seriously, if my boss hadn’t let me take the Developers Handbook home, and I didn’t own one other reference besides the textbook, and if I hadn’t gotten serious hints and help from the lounge, this would have been total disaster.
Thanks guys, and Charlotte.

Reply | Quote

Ordinarily I would agree with this. Which is why I only asked for a hint. However, for this class — There are no lectures, there are no other students, the instructor responds little or not at all, (online class). Not all of the information needed is included in the assigned or previously assigned material. We are apparently supposed to research it and figure it out.
Besides, my friend, who is a supervisor at Microsoft (really), told me that this is the way programmers work in real life. They do what they know how to do, hit the books, and if they can’t find it there, ask other programmers. So I am not only learning VBA, I’m learning to work like a real programmer/
I’m apparently learning a little advanced rationalization as well.
Seriously, if my boss hadn’t let me take the Developers Handbook home, and I didn’t own one other reference besides the textbook, and if I hadn’t gotten serious hints and help from the lounge, this would have been total disaster.
Thanks guys, and Charlotte.

Reply | Quote

Hans darlin’
We aren’t talking about the real world. This is a class assignment. It is MOST desirable for me to be able to complete it. I have the code that will delete the table, and the code that would delete all records, but they fail because of the relationship, I really really need to break the relationship. Your absolutly correct – Microsoft DAO library and the CurrentDb is exactly what I’m using. — Wait a minute. You said CurrentDb Relations collection. — I’m off to research, Thank you.

Reply | Quote

Although it is possible to change relationships in code, you must ask yourself if it is desirable. Let’s say that Table A has a primary key that is related to a foreign key in Table B. If you were to delete the relationship, then delete all records in Table A, the records in Table B would become orphaned: they have a foreign key that does not refere to any existing record in Table A. In most real-life situations that is undesirable.
One option is to first delete all records in Table B, and in all other tables with a foreign key that refers to the primary key in Table A. Only then delete the records in Table A.
Another option is to set Cascading Deletes for the relationships joining Table A to other tables. When you delete records from Table A, related records in other tables will automatically be deleted too.
If you need to manipulate relationships in code, the easiest way is to set a reference to the Microsoft DAO 3.6 Object Library, and work with the CurrentDb.Relations collection.

Reply | Quote