Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing Relationships Programmatically (2002)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing Relationships Programmatically (2002)

    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.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Relationships Programmatically (2002)

    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.

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Changing Relationships Programmatically (2002)

    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:

    <code>Public Sub DeleteRelation(ByRef TableName As String)</code>
    <code> On Error GoTo Err_Handler</code>
    <code></code>
    <code> Dim db As DAO.Database</code>
    <code> Dim n As Long</code>
    <code> Dim i As Integer</code>
    <code> Dim strMsg As String</code>
    <code></code>
    <code> Set db = CurrentDb</code>
    <code> </code>
    <code> ' Relations collection is zero-based</code>
    <code> For n = db.Relations.Count - 1 To 0 Step -1</code>
    <code> If db.Relations(n).Table = TableName Or _</code>
    <code> db.Relations(n).ForeignTable = TableName Then</code>
    <code> db.Relations.Delete db.Relations(n).Name</code>
    <code> i = i + 1</code>
    <code> End If</code>
    <code> Next n</code>
    <code> </code>
    <code> ' Test msg:</code>
    <code> If i > 0 Then</code>
    <code> MsgBox i & " relations for " & TableName & " table have been deleted.", _</code>
    <code> vbInformation, "RELATIONS DELETED"</code>
    <code> Else</code>
    <code> MsgBox "No relations found for this table (" & TableName & ").", vbInformation, "NO RELATIONS FOUND"</code>
    <code> End If</code>
    <code> </code>
    <code>Exit_Sub:</code>
    <code> Set db = Nothing</code>
    <code> Exit Sub</code>
    <code>Err_Handler:</code>
    <code> strMsg = "Error No " & Err.Number & ": " & Err.Description</code>
    <code> MsgBox strMsg, vbExclamation, "DELETE RELATION ERROR MSG"</code>
    <code> Debug.Print strMsg</code>
    <code> Resume Exit_Sub</code>
    <code> </code>
    <code>End Sub</code>

    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
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Relationships Programmatically (2002)

    <If this is for a class, isn't asking for answer online cheatin'??>
    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/ <img src=/S/clever.gif border=0 alt=clever width=15 height=15>
    I'm apparently learning a little advanced rationalization as well. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •