[RESOLVED] Creating a Primary Key

[tam57]

yes it is DAO and I will try what you suggest when I return to work - I still find it strange that it appears to create the key but the seek command does not recognise it - thanks for your help - TAM

[MartinLiss]

Some very old code of mine that could be improved by using Split().

Public Sub AddPrimaryKey(sTableName As String, sFieldName As String)

    Dim ndxIndex As Index
    Dim tdTableDef As TableDef
    Dim fldField As Field
    Dim nChars As Integer
    Dim nColons As Integer
    Dim sCurrChar As String
    Dim sFields(5) As String
    Dim nCtr As Integer
    
    On Error GoTo ErrorRoutine
    
    Erase sFields()
    
    'Parse the names of the fields to be included in the index
    For nChars = 1 To Len(sFieldName)
        sCurrChar = Mid(sFieldName, nChars, 1)
        If sCurrChar = ":" Then
            nColons = nColons + 1
            If nColons > 4 Then
                gsErrText = "Too many fields in index"
                Err.Raise 10007
            End If
        Else
            sFields(nColons) = sFields(nColons) & sCurrChar
        End If
    Next
    
    Set tdTableDef = gdbTargetDB.TableDefs(sTableName)
    
    'Create new Index object.
    Set ndxIndex = tdTableDef.CreateIndex(sFields(0))
    ndxIndex.Primary = True
    ndxIndex.Unique = True
    ndxIndex.Name = "PrimaryKey"
    
    For nCtr = 0 To nColons
        Set fldField = ndxIndex.CreateField(sFields(nCtr))
        ndxIndex.Fields.Append fldField
    Next
    
    'Save Index definition by appending it to Indexes collection.
    tdTableDef.Indexes.Append ndxIndex

ErrorRoutine:

    If Err.Number <> 0 Then
        PrintDetailRpt "AddPrimaryKey", FOUND_ERROR
    Else
        PrintDetailRpt "", OK
    End If
        

End Sub