How to Drop the Primary Key in Access

Knowing how to remove a primary key from a field using Microsoft Office Access can save you time when automating database processes. Visual Basic for Applications (VBA) is a computer programming language that can be used in Access to automate manual tasks. A primary key is used to identify rows in a table by giving it a unique value. In programming, removing a field that's assigned as a primary key can't be done so as with a normal field. Write VBA code to remove the primary key setting of a table column.

• 1

  Start Microsoft Office Access and select "Blank Database," then click "Create." Select the "Database Tools" menu, then click "Visual Basic." Select the "Insert" menu, then click "Module."

• 2

  Copy and paste the following code into your new module to create a table with a primary key and then remove it:

  Private Sub dropPrimaryKey()

  Dim strSQL As String

  strSQL = "CREATE TABLE pkTable "

  strSQL = strSQL & "(ID INTEGER CONSTRAINT PK_ID PRIMARY KEY, "

  strSQL = strSQL & "Address TEXT(25)) "

  DoCmd.RunSQL (strSQL)

  strSQL = "ALTER TABLE pkTable "

  strSQL = strSQL & "DROP CONSTRAINT PK_ID;"

  DoCmd.RunSQL (strSQL)

  End Sub

• 3

  Run your subroutine by pressing "F5."