Re: Delete primary key

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


"Allen Browne" wrote:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"NevilleT" <NevilleT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B1D4530F-AA7C-48F2-8D83-C4507CCAABD3@xxxxxxxxxxxxxxxx
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".

"NevilleT" wrote:

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.

"Allen Browne" wrote:

Is the index really named tblBenchmarks?

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"NevilleT" <NevilleT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CDD1C314-3F23-406E-A575-F1691F52BF8C@xxxxxxxxxxxxxxxx
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" wrote:

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

"NevilleT" <NevilleT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9F9D77C9-62BB-4BF1-A5F9-A80E643B4E31@xxxxxxxxxxxxxxxx
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.


.

Relevant Pages

  • Re: How to drop all the indexes of a table
    ... Allen Browne - Microsoft MVP. ... "Thomas" wrote in message ... Dim tdf As DAO.TableDef ... Set tdf = db.TableDefs ...
    (microsoft.public.access.queries)
  • Re: Is there a way to select all the field NAME by a SQL statement
    ... > Dim tdf As DAO.TableDef ... > Dim fld As DAO.Field ... > Set tdf = db.TableDefs ... > Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Grouped MAX Records?
    ... The subquery returns the primary key value of the record where the client ... Allen Browne - Microsoft MVP. ... >> Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.queries)
  • Re: subform records
    ... total records of nested subforms in a form ... Cancel the BeforeInsert event of the innermost subform if there are already ... tblSet, with SetID primary key, and OrderID foreign key. ... Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.formscoding)
  • Re: Delete primary key
    ... ' Add a new primary key to tblBenchmarks ... Set tdf = dbs.TableDefs ... Dim tdf As DAO.TableDef ... Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.modulesdaovba)