HandsOn Creating a Table with a Single Field Index

1. Open the Acc2003_Chap21.mdb file from the book's downloadable files, or create a new blank Access database to use for this chapter's procedures.

2. Switch to the Visual Basic Editor window and insert a new module.

3. In the module's Code window, enter the SingleField_Index procedure as shown below.

Sub SingleField_Index()

Dim conn As ADODB.Connection Dim strTable As String

On Error GoTo ErrorHandler

Set conn = CurrentProject.Connection strTable = "Supplier1"

conn.Execute "CREATE TABLE " & strTable _ & "(SupplierId INTEGER, " _ & "SupplierName CHAR (30), " _ & "SupplierPhone CHAR (12), " _ & "SupplierCity CHAR (19), " _ & "CONSTRAINT idxSupplierName UNIQUE " _

Programming with the Jet Data Definition Language

& "(SupplierName));" Application.RefreshDatabaseWindow ExitHere:

conn.Close Set conn = Nothing Exit Sub ErrorHandler:

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

End Sub

End Sub

Figure 21-1: The idxSupplierName index was created by running the procedure in Hands-On 21-1.
0 0

Post a comment