How to delete a relationship via code?

[DBLoser]

How do you delete and then recreate a relationship within code (command button). I'm having to import a dBASE file into access and the table is linked to another on a one-to-one basis. The original data table needs to be deleted and re-imported frequently but I can't delete it because of the relationship. I tried to just delete all of the data in the table and import into the existing table but from what I understand of the acImport command, it will create a new table if one of the same name exists (unless somebody can show me different). Here's the code:

DoCmd.TransferDatabase acImport, "dBASE III", "C:\", acTable, "NonConf", "NonConf"

When I run this it imports the NonConf table with fieldnames and data. If I run it again I get another NonConf table but it is called NonConf1. If I can delete the relationship of one of the fields prior to deleting the table and then recreate it after the import my problem will be solved.

Many thanks in advance,

Bruce

 

[RickSpr]

There are several solutions.

One is to change "acImport" to "acLink" in your TransferDatabase call. This will link the outside database rather than import it. You can then run an Append query to copy the data to your empty table. (Remember to delete the link afterward, using TableDefs.Delete.)

You can recreate the relationship if you'd rather. There are two ways to do that.

1. You can build a DDL "ALTER TABLE" statement to add a multifield constraint on the table, then execute the statement using DoCmd.RunSQL. Example:

    strDDL = "ALTER TABLE ChildTbl " _
        & "ADD CONSTRAINT MainTblChildTbl " _
        & "FOREIGN KEY (fk1, fk2, fk3) " _
        & "REFERENCES MainTbl (pk1, pk2, pk3) " _
        & "ON UPDATE CASCADE " _
        & "ON DELETE CASCADE"
    DoCmd.RunSQL strDDL

Replace "MainTbl" and "ChildTbl" with the names of your tables, and fkn and pkn with the names of the fields in the child table key and parent table key, respectively.
A multifield constraint is the equivalent of a relationship and its associated index in the child table.

2. You can use DAO to create the relationship. The following code sample, taken from the CreateRelation topic in the DAO Help file, does the same thing as the example above:

Sub CreateRelationX()
    Dim db As DAO.Database
    Dim tdfMainTbl As TableDef
    Dim tdfChildTbl As TableDef
    Dim relNew As Relation

    Set db = CurrentDb()
    With db
        Set tdfMainTbl = .TableDefs("MainTbl")
        Set tdfChildTbl = .TableDefs("ChildTbl")
        Set relNew = .CreateRelation("MainTblChildTbl", _
            "MainTbl", "ChildTbl", _
            dbRelationUnique Or dbRelationUpdateCascade _
            Or dbRelationDeleteCascade)
        With relNew
            Fields.Append .CreateField("fk1")
            Fields!fk1.ForeignName = "pk1"
            Fields.Append .CreateField("fk2")
            Fields!fk2.ForeignName = "pk2"
            Fields.Append .CreateField("fk3")
            Fields!fk3.ForeignName = "pk3"
        End With
        .Relations.Append relNew
    End With
    Set db = Nothing
End Sub

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[RickSpr]

Immediately after I submitted that, I realized I'd left out the code to delete a relationship, which is what your title was.

Using DDL: DoCmd.RunSQL "ALTER TABLE ChildTbl " _
& "DROP CONSTRAINT MainTblChildTbl"

Using DAO: db.Relations.Delete "MainTblChildTbl"

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein