Re: Drop Primary Key with SQL/VBA

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

From: Gary Walter (garylwpleasenospam_at_wamego.net)
Date: 04/05/04 

Date: Mon, 5 Apr 2004 03:46:24 -0500

"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 ... > then open new tblCustomer in design view, ...
    (microsoft.public.access.queries)
  • Re: Requery and stay on current record
    ... matches the data type of your primary key. ... If StudentID is an integer, ... KeyCurrent As Integer"; if StudentID is a string, ... Private Sub Command25_Click ...
    (microsoft.public.access.formscoding)
  • Re: Create Table Issues
    ... unique index on BookID. ... > CONSTRAINT PubPK PRIMARY KEY ... > CONSTRAINT PubFK FOREIGN KEY REFERENCES Publisher ...
    (microsoft.public.access.gettingstarted)
  • RE: Primary Keys w/ Multiple Columns: Dual Combo Boxes
    ... Make it the primary key. ... Keycode and Promo fields, make them required fields, and don't allow NULL's. ... Private Sub cboKeyCode_BeforeUpdate ... On Error GoTo ErrHandler ...
    (microsoft.public.access.formscoding)
  • Re: Why is my back end corrupting?
    ... The biggest clue to this corruption is that the key values are no longer ... marked as primary key after the compact/repair. ... affiliating that person with an organization. ... > Private Sub Combo0_NotInList ...
    (microsoft.public.access.forms)