[RESOLVED] Creating a Primary Key

[tam57]

Hi - I am using VB6 to create a primary key to an existing table in an Access database. It works but when I attempt to run the 'SEEK' command on that table a message returns saying that the primary key could not be found. Conversely, if I manually open the table and set the primary key the 'SEEK' command works. The code I am using for setting the primary key is as follows...

Dim strSQL As String
strSQL = "ALTER TABLE tablename ADD PRIMARY KEY(fieldname)"
dbs.Execute strSQL, dbFailOnError

As stated I know this works because I have opened the table afterwards and seen the primary key icon correctly established against the correct field so I can only imagine the code is missing some form of update command perhaps. Having said that if I close the database and then re-open it after running the above code the same error occurs. This has really got me stumped!

[Ellis Dee]

Is that DAO? If so, I'd think manipulating the TableDef would be the way to go.

[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

[vb5prgrmr]

Once you alter the table, any connections to the database or to the table have to be refreshed.

Good Luck

[tam57]

Great - that works. Thank you for the example - TAM

[MartinLiss]

Now that we've helped you, you can help us by marking the thread as resolved. If you have JavaScript enabled you can do that easily by pulling down the Thread Tools menu and selecting the Mark Thread Resolved item. Also if someone has been particularly helpful you have the ability to affect their forum "reputation" by rating their post. (No need to do it for me.) Only those ratings that you give after you have 20 posts will actually count, but in all cases the person you rate will see it and know that you appreciate their help.