Creating a Primary

A primary key uniquely identifies a row in a table. A primary key is an index with its Unique and PrimaryKey properties set to True. There can be only one primary key per table.

To create new keys, use the Key object from the ADOX library. To determine whether the key is primary, foreign, or unique, use the Key object's Type property. For example, to create a primary key, set the Key object's Type property to adKeyPrimary.

The procedure in Hands-On 12-1 demonstrates how to add a primary key to the table called tblFilters.

©Hands-On 12-1: Creating a Primary Key

This hands-on adds a primary key to the tblFilters table created in Hands-On 11-1 in Chapter 11. Import the module containing the Create_Table procedure code from the Acc2003_Chap11.mdb file to the current database and execute the procedure to create the tblFilters table. Or if you prefer, re-enter the Create_Table procedure code in the current database and run the procedure to create the required table.

1. Create a new Microsoft Office Access database or open the Acc2003_Chap12.mdb file from the book's downloadable files.

2. In the Database window, press Alt+F11 to switch to the Visual Basic Editor window.

3. In the Visual Basic Editor window, choose Insert | Module.

4. In the module's Code window, type the Create_PrimaryKey procedure shown below.

Creating and Manipulating Databases with ADO

5. Choose Run | Run Sub/UserForm to execute the procedure.

Sub Create_PrimaryKeyO

Dim cat As New ADOX.Catalog Dim myTbl As New ADOX.Table Dim pKey As New ADOX.Key

On Error GoTo ErrorHandler cat.ActiveConnection = CurrentProject.Connection Set myTbl = cat.Tables("tblFilters")

With pKey

.Name = "PrimaryKey" .Type = adKeyPrimary End With pKey.Columns.Append "Id" myTbl.Keys.Append pKey

Set cat = Nothing Exit Sub

ErrorHandler:

If Err.Number = -2147217856 Then

MsgBox "The 'tblFilters' is open.", _ vbCritical, "Please close the table" ElseIf Err.Number = -2147217767 Then myTbl.Keys.Delete pKey.Name Resume Else

MsgBox Err.Number & ": " & Err.Description End If End Sub

Notice that after setting the Name and Type properties of the Key object, the procedure appends the Id column to the Columns collection of the Key object. Next, the Key object itself is appended to the Keys collection of the table. Because errors could occur if a table is open or it already contains the primary key, the error handler is included to ensure that the procedure runs as expected.

0 0

Post a comment