Re: Delete primary key

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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: CONTSRAINT PRIMARY KEY
    ... PrimaryKey (don't ... >forget that a primary key may be more than one field) ... >Dim tdfCurr As DAO.TableDef ... >Dim fldCurr As DAO.Field ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Is field primary key or does it have a unique index
    ... Dim tdf As DAO.TableDef ... Dim idx As DAO.Index ... Set tdf = db.TableDefs ... If idx.Name = "PrimaryKey" Then ...
    (microsoft.public.access.modulesdaovba)
  • Re: more password user for more form in microsoft articles
    ... Create a compound PrimaryKey using ObjectName and KeyCode ... > Dim varHold As Variant ... > Set dbs = CurrentDb ...
    (microsoft.public.access.security)
  • Re: Indexing Tables
    ... In the primary key example (which is the one of most ... what does the "PrimaryKey" and "ID" ... > Dim tdf As DAO.TableDef ... > Dim ind As DAO.Index ...
    (microsoft.public.access.modulesdaovba)
  • Re: Please help, about primary key and dataset
    ... .PrimaryKey = New DataColumn() ... Object reference not set to an instance of an object. ... Dim CBuild As New OleDb.OleDbCommandBuilder ...
    (microsoft.public.dotnet.languages.vb)