Mega Code Archive
Creating a Table with a Validation Rule Referencing a Column in Another Table
<![CDATA[
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 = "myBook"
strTable2 = "myOrders"
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 ('999-99999-09', 5);", adExecuteNoRecords
conn.Execute "INSERT INTO " & strTable1 & " (ISBN,MaxUnits) Values ('333-55555-69', 7);", adExecuteNoRecords
conn.Execute "CREATE TABLE " & strTable2 & _
"(OrderNo AUTOINCREMENT CONSTRAINT" & _
"PrimaryKey PRIMARY KEY," & _
"ISBN CHAR, Items LONG," & _
"CONSTRAINT OnHandConstr CHECK" & _
"(Items <(Select MaxUnits from " & strTable1 & _
" 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
Else
Debug.Print Err.Number & ":" & Err.Description
Exit Sub
End If
End Sub
]]>