Re: Delete primary key

Tech-Archive recommends: Fix windows errors by optimizing your registry

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: Delete primary key
    ... Allen Browne - Microsoft MVP. ... primary key, add a new field and make ... For some reason it keeps failing with a syntax error. ... database to the linked database earlier in the procedure. ...
    (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: Delete primary key
    ... Dim tdf As DAO.TableDef ... Set tdf = db.TableDefs ... Allen Browne - Microsoft MVP. ... new field the primary key. ...
    (microsoft.public.access.modulesdaovba)
  • 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: Join of IP Addresses not working
    ... Allen Browne - Microsoft MVP. ... server and to Excel spreadsheets. ... Primary Key. ... the Mac Address table looks like this: ...
    (microsoft.public.access.queries)