Drop Primary Key with SQL/VBA

  • Thread starter Thread starter gorden blom
  • Start date Start date
G

gorden blom

Hi,

I want to drop the primary key from a table. I know I've got to delete
the constraint first. I'm working with Access 2000.

DoCmd.RunSQL ("ALTER TABLE " & strTabel & " DROP Constraint ID;")
DoCmd.RunSQL ("ALTER TABLE " & strTabel & " DROP PRIMARY KEY;")

The second line gives an error:

Error 3293:

Syntax error in ALTER TABLE statement.

can anyone help me out?

Thanks in advance,

Gorden Blom
 
gorden blom said:
Hi,

I want to drop the primary key from a table. I know I've got to delete
the constraint first. I'm working with Access 2000.

DoCmd.RunSQL ("ALTER TABLE " & strTabel & " DROP Constraint ID;")
DoCmd.RunSQL ("ALTER TABLE " & strTabel & " DROP PRIMARY KEY;")

The second line gives an error:

Error 3293:

Syntax error in ALTER TABLE statement.
Click to expand...
Hi Gorden,

Do you want to drop the Primary Key constraint on a field.....
or drop the field that is the Primary Key?

Either way, you will have to drop the constraint
and you can only do that if you

**know the name of the constraint.**

----------------------------------------
If you "let Access name the constraint,"
I don't know any way to find out what that
name is (except maybe guess).
----------------------------------------

If you create the constraint yourself,
either in a DDL Create SQL
or in a DDL Alter SQL,
then you know the name to use in your Drop query.

For example, in the following we end up
knowing the name of the pk constraint
so can drop it with following

ALTER TABLE tblShipping
DROP CONSTRAINT PK_CustomerID


1) -------------
CREATE TABLE tblShipping
(CustomerID INTEGER CONSTRAINT PK_CustomerID PRIMARY KEY,
Address TEXT(50),
City TEXT(50),
State TEXT(2),
Zip TEXT(10))
------------

2) ------------------------------
CREATE TABLE tblShipping
(CustomerID INTEGER,
Address TEXT(50),
City TEXT(50),
State TEXT(2),
Zip TEXT(10))

ALTER TABLE tblShipping
ALTER COLUMN CustomerID INTEGER
CONSTRAINT PK_CustomerID PRIMARY KEY
 
Gary Walter said:
Hi Gorden,

Do you want to drop the Primary Key constraint on a field.....
or drop the field that is the Primary Key?

Either way, you will have to drop the constraint
and you can only do that if you

**know the name of the constraint.**

----------------------------------------
If you "let Access name the constraint,"
I don't know any way to find out what that
name is (except maybe guess).
----------------------------------------

If you create the constraint yourself,
either in a DDL Create SQL
or in a DDL Alter SQL,
then you know the name to use in your Drop query.

For example, in the following we end up
knowing the name of the pk constraint
so can drop it with following

ALTER TABLE tblShipping
DROP CONSTRAINT PK_CustomerID


1) -------------
CREATE TABLE tblShipping
(CustomerID INTEGER CONSTRAINT PK_CustomerID PRIMARY KEY,
Address TEXT(50),
City TEXT(50),
State TEXT(2),
Zip TEXT(10))
------------

2) ------------------------------
CREATE TABLE tblShipping
(CustomerID INTEGER,
Address TEXT(50),
City TEXT(50),
State TEXT(2),
Zip TEXT(10))

ALTER TABLE tblShipping
ALTER COLUMN CustomerID INTEGER
CONSTRAINT PK_CustomerID PRIMARY KEY
-----------------------------
Click to expand...

Try:

ALTER TABLE tblShipping DROP
CONSTRAINT PK_CustomerID
 
Thanks..I'll remember that. 8-)

BTW, Your response to BradP
was "righton." It was all I could do
to not send a "me too."

gary
 
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)

Some knows what to do?

Thanks in advance,

Gorden Blom
 
gorden blom said:
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)
Click to expand...
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)?
 
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
 
Hi Gorden,

While looking for something else, I found
a routine in NEAT CD db that I adapted
into 2 functions that would have helped us
find what indexes you had on your table and
what their name(s) were so you could drop them.

Function GetPrimaryKeyName(pstrTableName As String) As String
'adapted from NEAT CD
' Gets the name of the primary key index of a table
' If none found, returns ""
On Error GoTo Err_GetPrimaryKeyName
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim indx As DAO.Index
Dim i As Integer
Set db = CurrentDb
Set tdf = db.TableDefs(pstrTableName)
GetPrimaryKeyName = ""
For i = 0 To tdf.Indexes.Count - 1
Set indx = tdf.Indexes(i)
If indx.Primary Then
GetPrimaryKeyName = indx.Name
Exit For
End If
Next i
db.Close

Exit_GetPrimaryKeyName:
Set tdf = Nothing
Set db = Nothing
Exit Function
Err_GetPrimaryKeyName:
If Err = 3265 Then
MsgBox "The table " & pstrTableName & " does not exist."
Resume Exit_GetPrimaryKeyName
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume Exit_GetPrimaryKeyName
End If
End Function

Function GetIndexes(pstrTableName As String) As Boolean
'adapted from NEAT CD
' Prints indexes in Immediate Window
On Error GoTo Err_GetIndexes
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim indx As DAO.Index
Dim i As Integer
Set db = CurrentDb
Set tdf = db.TableDefs(pstrTableName)
Debug.Print "TableName: " & pstrTableName
Debug.Print "Index Count: " & tdf.Indexes.Count
For i = 0 To tdf.Indexes.Count - 1
Set indx = tdf.Indexes(i)
Debug.Print vbCrLf & "Index Name: " & indx.Name
Debug.Print " Primary: " & indx.Primary
Debug.Print " Foreign: " & indx.Foreign
Debug.Print " Clustered: " & indx.Clustered
Debug.Print " Fields: " & indx.Fields
Debug.Print " Required: " & indx.Required
Debug.Print " Unique: " & indx.Unique
Debug.Print " Ignore Nulls: " & indx.IgnoreNulls

Next i
db.Close
GetIndexes = True

Exit_GetIndexes:
Set tdf = Nothing
Set db = Nothing
Exit Function
Err_GetIndexes:
GetIndexes = False
If Err = 3265 Then
MsgBox "The table " & pstrTableName & " does not exist."
Resume Exit_GetIndexes
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume Exit_GetIndexes
End If
End Function

Sigh...I guess I will know next time. 8-)

Gary Walter
 
Hi Gorden,

While looking for something else, I found
a routine in NEAT CD db that I adapted
into 2 functions that would have helped us
find what indexes you had on your table and
what their name(s) were so you could drop them.

Function GetPrimaryKeyName(pstrTableName As String) As String
'adapted from NEAT CD
' Gets the name of the primary key index of a table
' If none found, returns ""
On Error GoTo Err_GetPrimaryKeyName
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim indx As DAO.Index
Dim i As Integer
Set db = CurrentDb
Set tdf = db.TableDefs(pstrTableName)
GetPrimaryKeyName = ""
For i = 0 To tdf.Indexes.Count - 1
Set indx = tdf.Indexes(i)
If indx.Primary Then
GetPrimaryKeyName = indx.Name
Exit For
End If
Next i
db.Close

Exit_GetPrimaryKeyName:
Set tdf = Nothing
Set db = Nothing
Exit Function
Err_GetPrimaryKeyName:
If Err = 3265 Then
MsgBox "The table " & pstrTableName & " does not exist."
Resume Exit_GetPrimaryKeyName
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume Exit_GetPrimaryKeyName
End If
End Function

Function GetIndexes(pstrTableName As String) As Boolean
'adapted from NEAT CD
' Prints indexes in Immediate Window
On Error GoTo Err_GetIndexes
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim indx As DAO.Index
Dim i As Integer
Set db = CurrentDb
Set tdf = db.TableDefs(pstrTableName)
Debug.Print "TableName: " & pstrTableName
Debug.Print "Index Count: " & tdf.Indexes.Count
For i = 0 To tdf.Indexes.Count - 1
Set indx = tdf.Indexes(i)
Debug.Print vbCrLf & "Index Name: " & indx.Name
Debug.Print " Primary: " & indx.Primary
Debug.Print " Foreign: " & indx.Foreign
Debug.Print " Clustered: " & indx.Clustered
Debug.Print " Fields: " & indx.Fields
Debug.Print " Required: " & indx.Required
Debug.Print " Unique: " & indx.Unique
Debug.Print " Ignore Nulls: " & indx.IgnoreNulls

Next i
db.Close
GetIndexes = True

Exit_GetIndexes:
Set tdf = Nothing
Set db = Nothing
Exit Function
Err_GetIndexes:
GetIndexes = False
If Err = 3265 Then
MsgBox "The table " & pstrTableName & " does not exist."
Resume Exit_GetIndexes
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume Exit_GetIndexes
End If
End Function

Sigh...I guess I will know next time. 8-)

Gary Walter
 
Back
Top