Adding a Primary Key to a Table

You can use the ADD CONSTRAINT clause in the ALTER TABLE statement to define one or more columns as a primary key. Hands-On 19-10 defines a primary key for the tblSchools table created in Hands-On 19-2.

©Hands-On 19-10: Adding a Primary Key to a Table

This hands-on uses the tblSchools table created in Hands-On 19-2.

1. In the Visual Basic Editor window, insert a new module.

2. In the module's Code window, enter the AddPrimaryKey procedure as shown below.

Sub AddPrimaryKeyO

Dim conn As ADODB.Connection Dim strTable As String Dim strCol As String

On Error GoTo ErrorHandler

Set conn = CurrentProject.Connection strTable = "tblSchools" strCol = "SchoolId"

conn.Execute "ALTER TABLE " & strTable & _ " ADD CONSTRAINT pKey PRIMARY KEY " & _ "(" & strCol & ");" ExitHere:

conn.Close Set conn = Nothing Exit Sub ErrorHandler:

MsgBox Err.Number & ":" & Err.Description Resume ExitHere End Sub

0 0

Post a comment