HandsOn Creating a Table with a Validation Rule Referencing a Column in Another Table

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

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

Sub ValidateAgainstCol_InAnotherTbl() Dim conn As ADODB.Connection Dim strTable1 As String Dim strTable2 As String Dim InTrans As Boolean

On Error GoTo ErrorHandler

Set conn = CurrentProject.Connection strTable1 = "tblSupplies" strTable2 = "tblBookOrders"

conn.Execute "BEGIN TRANSACTION" InTrans = True conn.Execute "CREATE TABLE " & strTable1 & "(ISBN CHAR CONSTRAINT " & _ "PrimaryKey PRIMARY KEY, " & _ "MaxUnits LONG);", adExecuteNoRecords conn.Execute "Insert INTO " & strTable1 & " (ISBN,MaxUnits) " & _ " Values ('158-76609-09', 5);", _ adExecuteNoRecords conn.Execute "INSERT INTO " & strTable1 & " (ISBN,MaxUnits) " & _ " Values ('167-23455-69', 7);", _ adExecuteNoRecords

Part III

conn.Execute "CREATE TABLE " & strTable2 & _ "(OrderNo AUTOINCREMENT CONSTRAINT " & _ "PrimaryKey PRIMARY KEY, " & _ "ISBN CHAR, Items LONG, " & _ "CONSTRAINT OnHandConstr CHECK " & _ "(Items <(Select MaxUnits from " & strTablel & _ " WHERE ISBN = " & strTable2 & ".ISBN)));", _ adExecuteNoRecords conn.Execute "COMMIT TRANSACTION" InTrans = False Application.RefreshDatabaseWindow ExitHere:

conn.Close Set conn = Nothing Exit Sub ErrorHandler:

If InTrans Then conn.Execute "ROLLBACK TRANSACTION" Resume ExitHere


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

The procedure above creates two tables. Because the Items column in the tblBookOrders table needs to be validated against the contents of the MaxUnits column in the tblSupplies table, we wrapped the process of creating these tables and entering data in the tblSupplies table into a transaction. Because various errors could occur during the procedure execution, we declared a Boolean variable named InTrans to help us determine whether an error occurred during the transaction. Therefore, if the value of the InTrans variable is True, we will cancel the transaction. Notice that in Jet SQL syntax we use the BEGIN TRANSACTION statement to start the transaction, the COMMIT TRANSACTION statement to save the results of the transaction, and the ROLLBACK TRANSACTION statement to cancel any changes. Note that these transaction statements can only be used through the Jet OLE DB provider and ADO. They will cause an error when used with the Access user interface or DAO (Data Access Objects).

Notice that in this example procedure we used the adExecuteNoRecords option to specify that no rows should be returned. You can use this setting with the Connection or Command object's Execute method to improve performance when no rows are returned or when you don't plan to access the returned rows in your procedure code. If you omit this setting, your ADO code will still execute successfully, but the ADO will unnecessarily create a Recordset object as the return value for the Execute method. Using the adExecuteNoRecords setting is one of several techniques of optimizing data access using ADO.

3. Run the above procedure to create the tblBookOrders table.

4. Open the tblBookOrders table and enter the record shown at the top of Figure 20-2.

Programming with the Jet Data Definition Language

5. When you try to save this record or move to the next data row, Access will display a message informing you that the value you are trying to enter is prohibited.

6. Enter the value of 4 in the Items column. This time Access approves of the entry and no error message is displayed.

7. Close the tblBookOrders table.

Figure 20-2: When you attempt to enter a value that does not meet the validation rule, Microsoft Office Access displays an error message.

8. In the Database window, right-click the tblBookOrders table and choose Delete. Click Yes to confirm the deletion.

9. Access will respond with the error message shown in Figure 20-3.

Microsoft Office Access

Figure 20-3: If you try to manually delete a table referenced by the CHECK constraint, Microsoft Office Access will display an error message.

Now, let's see how you can use the Access user interface to issue commands that delete tables and CHECK constraints.

0 0

Post a comment