G
Guest
This is a bit unusual but it is part of a table upgrade with a new release of
software. One of the tables needs to be changed to delete the existing
primary key (the index, not the field itself), add a new field and make the
new field the primary key. I can successfully add the new field
(BenchmarkNo) but have been unable to change the old PK (ProjectNo) into just
an index.
I used two approaches. The first was to use the ALTER in SQL. The
debug.print of the sql is:
ALTER tblBenchmarks DROP PRIMARY KEY, ADD PRIMARY KEY ("BenchmarkNo");
For some reason it keeps failing with a syntax error.
The second approach was to set the primary value to false. I set the
database to the linked database earlier in the procedure. I can step through
the code and it locates the primary key:
With tdf
.Fields.Append .CreateField("BenchmarkNo", dbAutoIncrField)
For Each idx In tdf.Indexes
If idx.Primary = True Then
idx.Primary = False
End If
Next
End With
The error I get is "Cannot set the property once the object is part of a
collection." when the code tries to set the primary key to false.
After a few hours on this I would appreciate some guidance.
software. One of the tables needs to be changed to delete the existing
primary key (the index, not the field itself), add a new field and make the
new field the primary key. I can successfully add the new field
(BenchmarkNo) but have been unable to change the old PK (ProjectNo) into just
an index.
I used two approaches. The first was to use the ALTER in SQL. The
debug.print of the sql is:
ALTER tblBenchmarks DROP PRIMARY KEY, ADD PRIMARY KEY ("BenchmarkNo");
For some reason it keeps failing with a syntax error.
The second approach was to set the primary value to false. I set the
database to the linked database earlier in the procedure. I can step through
the code and it locates the primary key:
With tdf
.Fields.Append .CreateField("BenchmarkNo", dbAutoIncrField)
For Each idx In tdf.Indexes
If idx.Primary = True Then
idx.Primary = False
End If
Next
End With
The error I get is "Cannot set the property once the object is part of a
collection." when the code tries to set the primary key to false.
After a few hours on this I would appreciate some guidance.