Set Primary Key via VBA

[Dan]

I'm using the following code in an attempt to modify the Primary key in a
linked table upon database startup. When the code reaches the
"idxCurr.Primary = False" statement I receive the following error: "Cannot
set this property once the object is part of a collection." Can anyone
explain what I'm running into and a possible solution? Thanks.

CODE---------
'Delete existing relationships between master product tables
dblink.Relations.Delete "MasterProdListMasterProdAppList"
dblink.Relations.Delete "MasterProdListMasterProdLabelGrp"
dblink.Relations.Delete "MasterProdListMasterProdLaundry"
dblink.Relations.Delete "MasterProdListMasterProdSanType"

'Modify key field of master product tables.
Set tdf = dblink.TableDefs("MasterProdList")
For Each idxCurr In tdf.Indexes
idxCurr.Primary = False
Next idxCurr
Set idxCurr = tdf.CreateIndex("ProdGrp")
With idxCurr
.Fields.Append .CreateField("ProdGrp")
End With
idxCurr.Primary = True
tdf.Indexes.Append idxCurr
Set idxCurr = Nothing
Set tdf = Nothing

 

[Brendan Reynolds]

The property is read-only after the index has been appended to the indexes
collection.

I believe you'll have to drop the existing primary key index and create a
new one, rather than trying to change properties of an existing index.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.