NEED HELP WITH PRIMARY KEYS

[tcarter]

I need additional help with primary keys.

I have a table with three fields.

Mat_Lib_tbl
**************
1. SSN
2. LibraryID (PK)
3 Name


I want to programmatically change the table design to drop
the "LibraryID" as the primary key and assign the primary
key to be "SSN".

So that the alter table will look like this....

1. SSN (PK)
2. LibraryID
3. Name

How can this be done through code?

 

[Douglas J. Steele]

Assuming you're using DAO, you can either set the Primary property of the
existing index to False, or delete it from the Indexes collection. You can
use CreateIndex to create the new index and set its Primary property to
True.

 

[Guest]

Thanks for your help.

But can you provide source code based on the example I
gave?

Using the example below, I want to change the Primary Key
to SSN instead of Library_ID.

But, I want to keep Library_ID indexed. How is that done
through code.


Please read below! Thanks so much

 

[Douglas J. Steele]

The following is untested air code (and requires that a reference to DAO
exist)

Sub ChangePK()
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim idxCurr As DAO.Index

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs("Mat_Lib_tbl")
For Each idxCurr In tdfCurr.Indexes
idxCurr.Primary = False
Next idxCurr
Set idxCurr = tdfCurr.CreateIndex("SSN")
With idxCurr
.Fields.Append .CreateField("SSN")
End With
idxCurr.Primary = True
tdfCurr.Indexes.Append idxCurr

Set idxCurr = Nothing
Set tdfCurr = Nothing
Set dbCurr = Nothing
End Sub