Cannot DROP COLUMN with SQL - How To Design Tables, Set Relationships, Create Views, and Enforce Integrity in Microsoft Access SQL


	Back to SQL 3 Seminar Lessons Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Cannot DROP COLUMN with SQL

Sami Shamma

2 years ago

Hi Friends

Does anyone know why I cannot drop a Primary Key CLOUMN in a table?
The Column was created by SharePoint. I am moving the Backend to SQL Server and cleaning all the extra columns SharePoint placed in the table. It will not let me drop the Index either.

It does not generate and error but does not delete.
Note: I can manually Delete that column.

Thanks

Details
Private Sub Button2_Click()
    'Clean Court, Judge and User Table
    'Make a copy of the table
    'DoCmd.CopyObject , "JudgeT-OLD", acTable, "JudgeT"
    'DoCmd.CopyObject , "CourtT-OLD", acTable, "CourtT"
    'DoCmd.CopyObject , "UserT-OLD", acTable, "UserT"

    'DropField "CourtT"
    'DropField "judgeT"
    DropField "UserT1", "UserID", "UserIDSP"
    
End Sub

Private Sub DropField(TableName As String, PrimaryKey As String, OldID As String)
    
    Dim MySQL As String
    Dim MySQL2 As String
    
    MySQL = "ALTER TABLE " & TableName & " ALTER COLUMN UserIDSP INTEGER"


    MySQL2 = "ALTER TABLE " & TableName & _
            " DROP COLUMN " & OldID & "," & _
            " [Title], " & _
            " [Color Tag], " & _
            " [Compliance Asset Id], " & _
            " [Content Type], " & _
            " [App Created By], " & _
            " [App Modified By], " & _
            " [Workflow Instance ID], " & _
            " [File Type], " & _
            " [Modified], " & _
            " [Created], " & _
            " [Created By], " & _
            " [Modified By], " & _
            " [URL Path], " & _
            " [Path], " & _
            " [Item Type], " & _
            " [Encoded Absolute URL];"
    
    MsgBox MySQL
    'DoCmd.RunSQL MySQL

End Sub

In the code above, all the remaining columns are dropped.

	Kevin Yip @Reply 2 years ago
To drop the primary index, you need to use: ALTER TABLE TableName DROP CONSTRAINT IndexName In Access, the index name is found in table design -> indexes.

	Sami Shamma OP @Reply 2 years ago
Hi Kevin Yes, I managed to find that. My current problem is that I need to drop all Indexes before I can drop the columns. I am trying to automate this Data migration without human intervention. Different tables have different Indexes. Is there a way in SQL to get all the indexes of a table and the loop and delete all of them? Many Thanks

Kevin Yip @Reply

2 years ago

The index names are stored in the sys.indexes table, in the [name] field.  This SQL will list all the index items in [TableName]:

     SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('TableName');

To avoid having to dig into system tables to look up names you don't know, you may name the indices yourself when you create tables.  E.g.:

     CREATE TABLE TableName
     (
          PKFieldName int CONSTRAINT PKIndexName PRIMARY KEY IDENTITY (1,1),
          FieldName1 nvarchar(255)
     )

	Sami Shamma OP @Reply 2 years ago
Thank you, Kevin. I am naming the new index keys. It is cleaning up the mess created by SharePoint that the issue. Appreciate your help.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in SQL 3 Seminar Lessons.

Next Unseen

New Feature: Comment Live View