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

[gesrod]

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.

 

[alvechurchdata]

I don't know how to discover whether a primary key exists but can you not just check for this error number in your error handler?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[gesrod]

Good idea . . will try that

 

[jrbarnett]

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 (

http://sourceforge.net/mdbdoc).

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

 

[jrbarnett]

The link to the project should read

http://sourceforge.net/projects/mdbdoc/

John

 

[gesrod]

Thanks for that John, I'll give that a go and let you know.