Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    267

    Unanswered: SQL to Delete Relationships

    Here is the deal, I need to write ADO Code or SQL statement to delete relationships in an existing DB.

    I would just go and manually delete the relationships, however, this DB is at multiple locations, and my customer, doesn't want to interupt each location for a day while they send me the database, and i make all the structures changes I need to (a lot of more changes then just deleting relationships, due to a major requirement change), and then send it back.

    I am in the processing of writing a VB script to do all the changes I need, but deleting relationships is my last road block.

    I'm assuming that with SQL I need an "Alter Tabel Del Constriant" statement, but I can't find any examples of the syntax.


    Thanks.

    S-

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: SQL to Delete Relationships

    Originally posted by sbaxter
    Here is the deal, I need to write ADO Code or SQL statement to delete relationships in an existing DB.

    I would just go and manually delete the relationships, however, this DB is at multiple locations, and my customer, doesn't want to interupt each location for a day while they send me the database, and i make all the structures changes I need to (a lot of more changes then just deleting relationships, due to a major requirement change), and then send it back.

    I am in the processing of writing a VB script to do all the changes I need, but deleting relationships is my last road block.

    I'm assuming that with SQL I need an "Alter Tabel Del Constriant" statement, but I can't find any examples of the syntax.


    Thanks.

    S-
    Using ADO, I was able to remove relationships like this:

    Private Sub cmdRemoveRelationship_Click()

    Dim cat1 As ADOX.Catalog, tbl As ADOX.Table, key1 As ADOX.key

    Set cat1 = New ADOX.Catalog
    cat1.ActiveConnection = cnn
    (I declared and set the connection previously)
    Set tbl = cat1.Tables("Table Name")
    Set key1 = tbl.Keys("Key Name")
    tbl.Keys.Delete(Key1.Name)

    Set cat1 = Nothing
    Set tbl = Nothing
    Set key

    End Sub

    ...Or something to that effect.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  3. #3
    Join Date
    Nov 2003
    Posts
    267
    Thanks basicmek

    That worked great,

    out of curosity, is there a way to do this with an SQL statement?

    S-

  4. #4
    Join Date
    Feb 2004
    Posts
    199
    IF you're connected to MS SQL , you can do anything via SQL batch, but I doubt that there is no way to do this in MDB (jet)
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

Posting Permissions

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