How to Alter a Primary Key

Knowing how to alter a primary key can save you time when working with a table that needs a primary key removed. Microsoft Office Access is a relational database management system often used to design local databases. A primary key is used as a unique identifier for each record in a database table. Visual Basic for Applications, also known as VBA, is a computer programming language used to automate tasks otherwise done manually in Microsoft Office applications.

Things You'll Need

• Microsoft Office Access

Instructions

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. Type the following to create a new subroutine:

   Private Sub alterPrimaryKey()

   Press "Enter."

3. Type the following to create a new String variable:

   Dim SQLstr As String

4. Type the following to create a new table with two columns, using "ID_Field" as the primary key and "Column1" as a text column:

   SQLstr = "CREATE TABLE myTbl "

   SQLstr = SQLstr & "(ID_Field INTEGER CONSTRAINT PK_ID_Field PRIMARY KEY, "

   SQLstr = SQLstr & "Column1 TEXT(25)) "

   DoCmd.RunSQL (SQLstr)

5. Type the following to remove the primary key setting from the "ID_Field":

   SQLstr = "ALTER TABLE myTbl "

   SQLstr = SQLstr & "DROP CONSTRAINT PK_ID_Field;"

   DoCmd.RunSQL (SQLstr)

6. Press "F5" to run the subroutine.

References

• Microsoft: ALTER TABLE - Dropping a constraint