Creating multiple PK fields in table

[smig]

I need to reate multiple PK fields on table.
It need to be done using VBA as I have no access to the client's BE db, only to the FE one (Sending him a new version).
This is a long going (few years) developed app.

I can easily add a single field PK using Alter Table Alter Column routines.

I tried using the code below but it will work for the first field only. For the second one I get an error saying a PK is already exist

' --- Set PK (As part of the Index)
strSQL = "SELECT * FROM [NewVersion_Tables_Columns] " & _
    "WHERE [dbVersion] > " & LatestDataMDBVersion(strDataMDBFile) & " " & _
    "AND Trim([ColumnName] & '') <> '' " & _
    "AND [ColumnPrimaryKey] = True " & _
    "AND (LCase([Modify]) = 'alter' or LCase([Modify]) = 'add') " & _
    "ORDER BY [TableName], [ColumnName] "

Set rs = CurrDB.OpenRecordset(strSQL)
With rs
    If .RecordCount > 0 Then
        .MoveFirst
        Do While Not .EOF
            strTableName = .Fields("TableName")
            strColumnName = .Fields("ColumnName")
            ' Create and append a new Index object to the
            ' Indexes collection of the new TableDef object.
            Set indx = DataDB.TableDefs(strTableName).CreateIndex("PrimaryKey")
            indx.Fields.Append indx.CreateField(strColumnName)
            indx.Primary = True
            DataDB.TableDefs(strTableName).Indexes.Append indx
            .MoveNext
        Loop
        .Close
    End If
End With

I have a table ([NewVersion_Tables_Columns]) that hold all the columns I want to make as PK


Thanks,
Tal

 

[CJ_London]

you can only have one primary key per table (and one autonumber field which is usually the primary key)

You can make other fields indexed, no duplicates

 

[smig]

you can only have one primary key per table (and one autonumber field which is usually the primary key)

You can make other fields indexed, no duplicates

I have many tables with multiple fields as the PK

This table act as Many-To-Many connection

 

[CJ_London]

do you mean PK or do you mean FK?

 

[smig]

do you mean PK or do you mean FK?

I mean PK that is based on several fields.

SOLVED
I needed 2 loops:
1st. to create the Table's Index.
2nd. Append the Fields to the Index.
After the 2nd loop is done the newly created Index is appended to the table (End of 1st. loop)

' --- Set PK (As part of the Index)
strSQL = "SELECT [TableName] FROM [NewVersion_Tables_Columns] " & _
    "WHERE [dbVersion] > " & LatestDataMDBVersion(strDataMDBFile) & " " & _
    "AND Trim([ColumnName] & '') <> '' " & _
    "AND [ColumnPrimaryKey] = True " & _
    "AND (LCase([Modify]) = 'alter' or LCase([Modify]) = 'add') " & _
    "GROUP BY [TableName] "

Set rsPKTables = CurrDB.OpenRecordset(strSQL)
With rsPKTables
    If rsPKTables.RecordCount > 0 Then
        rsPKTables.MoveFirst
        Do While Not rsPKTables.EOF
            strTableName = rsPKTables.Fields("TableName")
        
                Set indx = DataDB.TableDefs(strTableName).CreateIndex("PrimaryKey")
                            
                strSQL = "SELECT * FROM [NewVersion_Tables_Columns] " & _
                    "WHERE [dbVersion] > " & LatestDataMDBVersion(strDataMDBFile) & " " & _
                    "AND Trim([ColumnName] & '') <> '' " & _
                    "AND [ColumnPrimaryKey] = True " & _
                    "AND (LCase([Modify]) = 'alter' or LCase([Modify]) = 'add') " & _
                    "AND [TableName] = '" & strTableName & "' "
                
                Set rs = CurrDB.OpenRecordset(strSQL)
                With rs
                    If rs.RecordCount > 0 Then
                        rs.MoveFirst
                        Do While Not rs.EOF
                            strColumnName = .Fields("ColumnName")
                            ' Create and append a new Index object to the
                            ' Indexes collection of the new TableDef object.
                            indx.Fields.Append indx.CreateField(strColumnName)
                            
                            rs.MoveNext
                        Loop
                        rs.Close
                    End If
                End With
                                
                indx.Primary = True
                DataDB.TableDefs(strTableName).Indexes.Append indx
                DataDB.TableDefs(strTableName).Indexes.Refresh

            rsPKTables.MoveNext
        Loop
        rsPKTables.Close
    End If
End With

 

[gemma-the-husky]

I think the vba to create a multi-column index is incorrect.

Set indx = DataDB.TableDefs(strTableName).CreateIndex("PrimaryKey")

after this, you need to define the fields that make up the index. An index has a fields collection.

indx.fields.add "fieldname" - something like that. Not sure of the exact syntax without looking it up. Because you want several fields in the index, you need to add each of them to the fields collection of the index.

 

[smig]

I had some garbage in the code

this line will add the field to the Index's fields collection (It's in the inner loop)
indx.Fields.Append indx.CreateField(strColumnName)

 

[gemma-the-husky]

oh yes, I see the code line now.

 

[AccessBlaster]

you can only have one primary key per table (and one autonumber field which is usually the primary key)

You can make other fields indexed, no duplicates

Try holding the "shift" key while creating another primary key in design view.