In MS Access, I have a ZipCode table that is related via foreign key to Company and Customer. I need to delete the ZipCode table (from VB6 ADOX code) in order to replace it with one of a different structure, containing significant new data.

I have the following code:

    Dim objTable as ADOX.Table
    Dim objKey as ADOX.Key

    Set objTable = mCat.Tables("ZipCode")
    For Each objKey in objTable.Keys
        MsgBox objKey.Name
    Next objKey

The only key displayed is PrimaryKey. The Key.Count of the table is 1. Same goes for Indexes.

Does anyone know how to delete a foreign key?

TIA, Pete

Sorry! Just found the answer. The foreign key appeared under the Keys collection of the other table, not that of ZipCode. Everything is better now...thanks for listening!

Pete

Thought I had it knocked, but just ran into a subsequent snag. When I try to delete the key, I'm getting error -2147467259: "Cannot delete this index or table - it is either the current index or is used in a relationship."

Here's the code:

        Set mobjTable = mCat.Tables("Customer")
        For Each objIndex In mobjTable.Indexes
            If objIndex.Name = "ZipCodeCustomer" Then
                mobjTable.Indexes.Delete "ZipCodeCustomer"
            End If
        Next objIndex

objIndex doesn't have a delete method, so the above delete was the only way I could figure out to do it. Does anyone know how to delete a relationship programmatically?

TIA, Pete

Here is a way to do it with DAO
****
Public Sub DestroyIndex_DAO(MyDB As String, MyIndexTable As String, MyIndexName As String)
On Error Resume Next

'MyDB is the full path and filename where the table is located
'MyIndexTable is the table where the index is located
'MyIndexName is the name of the index to be deleted

    Dim MyWorkspace As Workspace
    Dim MyDatabase As Database
    Dim MyTableDef As TableDef
    Dim MyIndexField As String

    Set MyWorkspace = DBEngine.Workspaces(0)
    Set MyDatabase = MyWorkspace.OpenDatabase(MyDB)

        Set MyTableDef = MyDatabase.TableDefs(MyIndexTable)
            With MyTableDef
                .Indexes.Delete MyIndexName
            End With

End Sub