Delete primary key

[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.

 

[Allen Browne]

Drop the index. Then create the primary key index on the new field (and also
the ordinary index on the old field if desired.)

DAO (your 2nd approach) is probably the easiest way.
Here's now to delete an index:
http://allenbrowne.com/func-DAO.html#DeleteIndexDAO
and create one:
http://allenbrowne.com/func-DAO.html#CreateIndexesDAO

 

[Guest]

Hi Allen
I had looked at the example below (your site is always the first stop when I
get stuck). I am missing something. The example you pointed me at sets up a
foreign key and deletes it. I tried the following and it failed.

Dim rel as Relation
dbs.Relations.Delete "tblBenchmarks"

and it failed with "Item not found in this collection" Should I be deleting
a relation or an index. Very confused.

 

[Allen Browne]

Is the index really named tblBenchmarks?

Or did you need:
dbs.TableDefs("tblBenchmarks").Indexes.Delete "WhateverItsCalled"

 

[Guest]

Hi Allen

What you say makes sense. My next problem is to work out what the index
name is. Suspect it is something obvious like "Primary". Will fiddle a bit
more. Thanks again. I am getting closer.

 

[Guest]

Hi Allen

You are getting me pointed in the right direction. What I need to find out
now is the name of the index. I suspect it is something obvious like
"Primary".

 

[Allen Browne]

Chances are it's named "PrimaryKey"

You can open the table in design view, and then open the Indexes dialog to
see the names of the (non-hidden) indexes.

Or you can list all the indexes like this:

Function ShowIndexes(strTable As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdf = db.TableDefs(strTable)
For Each ind In tdf.Indexes
Debug.Print ind.Name, IndexDescrip(ind)
Debug.Print " Field(s): ";
For Each fld In ind.Fields
Debug.Print fld.Name;
Next
Debug.Print
Debug.Print " " & IndexDescrip(ind)
Debug.Print
Next

Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
End Function

 

[Guest]

Thanks Allen. It makes sense now. All I have to do is find the name of the
index. It is probably something obvious like "Primary".

 

[Guest]

OK. This works. Thanks a lot Allen for getting me on the right track.

' Add a new primary key to tblBenchmarks
' Delete the existing primary key
Set tdf = dbs.TableDefs("tblBenchmarks")
With tdf
For Each idx In .Indexes
If idx.Name = "PrimaryKey" Then
.Indexes.Delete "PrimaryKey"
End If
Next

' Add the new primary key field
Set fld = .CreateField("BenchmarkNo", dbLong)
fld.Attributes = .Attributes Or dbAutoIncrField
.Fields.Append fld

' Set the new field as the PK
Set idx = .CreateIndex("PrimaryKey")

With idx
.Fields.Append .CreateField("BenchmarkNo")
.Primary = True
End With

.Indexes.Append idx

End With

Set fld = Nothing
Set tdf = Nothing
Set dbs = Nothing