How to set primarykey to a field by VBA or Marco?

[jefferyzhong]

Could anyone tell me how to set primarykey to a field by VBA or Marco?
I need to update a table once a week, and I need Primarykey for that table.

Thanks

 

[Remou]

Hi
Do you mean (?):

Set db = CurrentDb
Set tdf = db.TableDefs("TableName")
For Each idx In tdf.Indexes
    If idx.Primary Then
        Debug.Print idx.Name
        Debug.Print idx.Fields
    End If
Next

 

[jefferyzhong]

for example, I want the table of "TblEmployee", the field of "EmployeeID" to be the PrimaryKey. just by clicking the command button. not by setup through design view of the table because I do not want everybody to touch the structure of the database.

Thanks

 

[Remou]

This might suit:

Set db = CurrentDb
Set tdf = db.TableDefs("tblEmployee")
'Create index
Set idxnew = tdf.CreateIndex("EmpID")
'Add fields for index
idxnew.Fields.Append idxnew.CreateField("EmployeeID")
'Primary key
idxnew.Primary = True
'Add index to indexes collection
tdf.Indexes.Append idxnew

For Each idx In tdf.Indexes
    If idx.Primary Then
        Debug.Print idx.Name
        Debug.Print idx.Fields
    End If
Next

This code will alter your table, if I have it right, if not, who knows what it will do? So you need a back up as I only tested lightly.