Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old February 2nd, 2005, 04:19 PM
Authorized User
Join Date: Jun 2003
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default Delete MS Access Foreign Key from Code

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
Old February 2nd, 2005, 04:28 PM
Authorized User
Join Date: Jun 2003
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts

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!

Old February 2nd, 2005, 06:51 PM
Authorized User
Join Date: Jun 2003
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts

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
Old February 21st, 2006, 06:45 PM
Registered User
Join Date: Feb 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Foreign key not updating with Primary key xavier1945 BOOK: Access 2003 VBA Programmer's Reference 2 July 4th, 2007 09:48 PM
Composite Foreign Key bijgupt SQL Server 2005 6 May 4th, 2007 08:46 AM
FOREIGN KEY and PRIMARY KEY Constraints junemo Oracle 10 June 15th, 2004 01:00 AM
Foreign Key Problem re_anu SQL Server 2000 1 September 26th, 2003 10:58 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.