Re: Drop Primary Key with SQL/VBA

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: gorden blom (gordenblom_at_hotmail.com)
Date: 04/07/04 

Date: 6 Apr 2004 23:22:59 -0700

Gary,

Thanks for your replay! Your code works for me too. I've rebuild my
table and everything works fine now. I'm still wandering why it didn't
work earlier, when I've found the error I will post back with the
anwser.

This field had no relationships with other, and it was inside my
access DB. Thanks for your help in the last couple of day's I've
appriciated your help.

Gorden Blom

"Gary Walter" <garylwpleasenospam@wamego.net> wrote in message news:<uBS4PtuGEHA.3856@TK2MSFTNGP12.phx.gbl>...
> "gorden blom" <gordenblom@hotmail.com> wrote in message
> news:8c25c8c2.0404042237.725633cd@posting.google.com...
> > Hi,
> >
> > Thanks for all the replies. I know the name of the constraint, it's
> > been given the same way, as in your example. When I dropped the
> > constraint there still is the primary key on that field. I can't drop
> > this field until I've removed the primary key.
> >
> > When I try to drop the field I get the following error:
> >
> > Error 3280:
> >
> > Cannot delete a field that is part of an index or is needed by the
> > system.
> >
> > I think it can only be done in the following way:
> > 1. Drop the constraint(done that)
> > 2. Drop the primary key
> > 3. Drop the field (can only be done if I first do nr. 2)
> >
> Hi Gorden,
>
> If I save the following in a module:
>
> Public Sub CreateTable()
> Dim strSQL As String
>
> strSQL = "CREATE TABLE tblCustomer " _
> & "(CustomerID INTEGER CONSTRAINT PK_CustomerID PRIMARY KEY, " _
> & "Address TEXT(50), " _
> & "City TEXT(50), " _
> & "State TEXT(2), " _
> & "Zip TEXT(10));"
> CurrentDb.Execute strSQL, dbFailOnError
>
> End Sub
>
> Public Sub DropPK()
> Dim strSQL As String
>
> strSQL = "ALTER TABLE tblCustomer " _
> & "DROP CONSTRAINT PK_CustomerID;"
> CurrentDb.Execute strSQL, dbFailOnError
>
> End Sub
>
> If I run the sub CreateTable(),
> then open new tblCustomer in design view,
> with CustomerID selected,
> I see:
>
> a "key" beside CustomerID
>
> and in "General":
>
> Field Size Long Integer
> Decimal Places Auto
> Required No
> Indexed Yes (No Duplicates)
> (all other rows have no value)
>
> Then, if I run the sub DropPK(),
> then open tblCustomer in design view,
> with CustomerID selected,
>
> I see:
>
> no "key" beside CustomerID
>
> and in "General"
>
> Field Size Long Integer
> Decimal Places Auto
> Required No
> Indexed No
> (all other rows have no value)
>
>
> **And I can delete the field CustomerID.***
>
> So...why does this not work for you?
>
> What is the difference in the "General" tab
> before and after you run your drop constraint SQL?
>
> Did you create the constraint in SQL
> so you for sure know its name?
>
> Is the field also part of a relationship?
>
> Is this field in an Access table (or some
> other db)?

Relevant Pages

  • Re: Drop Primary Key with SQL/VBA
    ... > constraint there still is the primary key on that field. ... Public Sub CreateTable() ... Dim strSQL As String ...
    (microsoft.public.access.queries)
  • Re: Cancel/Terminate Connection from the control
    ... Private sub getData() ... >> from SQL server database. ... If I need to cancel this request while it is ... >> Dim strsql as string ...
    (microsoft.public.vb.database.ado)
  • Re: Cancel button to undo data on subform
    ... Private Sub cmdCancel_Click ... Dim strSQL As String ... I would certainly check before running a Delete query though. ... I have set up message boxes in most of the forms, ...
    (microsoft.public.access.formscoding)
  • Re: Add a new record to a third table
    ... Private Sub Form_AfterUpdate ... >> Dim strSql As String ... >> DBEngine.Execute strSql, dbFailOnError ... >>> literal value, change it to an Append query, switch to SQL ...
    (microsoft.public.access.formscoding)
  • RE: List Box / Combo Box
    ... Combo all you need to do is set the sub form's Fitler property and set the ... Filter property to True. ... a subform open to datasheet and stated list box. ... Dim strSQL As String ...
    (microsoft.public.access.forms)