Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

See If a Constraint Exists (Primary Key) in an access table

Status
Not open for further replies.

gesrod

Technical User
Jan 30, 2009
8
GB
I am wanting to 'DROP Constraint' on a table by using the 'Alter Table' in vb code, this works fine if the field is the primary key, but if it's not it errors with 'CHECK constraint 'fieldname' does not exist'. Is there a way of using lookup to find if primary key exists before I try and drop it ?

in anticpation.
 
To tell if a field is part of the primary key of a table, you can use this code.

It is lifted straight from the source code of my MDB Doc access documenter (
Code:
Public Function mdbdIsPK(tdf As DAO.TableDef, fld As DAO.Field) As Boolean
    'MDBDOC: Returns True/False depending whether a specific field is part of a specific tables primary key.
    ' Function: mdbdIsPK
    ' Parameters: tdf - Tabledef; fld Field
    ' Return Value: Boolean - True if fld is in primary key of table tdf, false otherwise
    ' Author:   John Barnett
    ' Date:     10 July 2001.
    ' Description: IsPK returns a True/False value depending whether field fld is part of the primary key of table pk.
    ' Called by: Fields section of ProcessDatabase

    Dim idx As Index

    mdbdIsPK = False
    For Each idx In tdf.Indexes
        ' Loop through each of the table indexes
        If InStr(idx.Fields, fld.Name) Then
            ' if the field name is in the index and the index is the primary one then it is true
            If idx.Primary = True Then
                mdbdIsPK = True
                Exit For
            End If
        End If
    Next idx
End Function

Call it from a loop going through your table and fields collection (a for each) loop.

John
 
Thanks for that John, I'll give that a go and let you know.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top