в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:46:45Z

1 версия

Новая страница

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



<source lang="vb">

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
</source>

==Relating Two Tables and Setting up Cascading Referential Integrity Rules==



<source lang="vb">

Sub RelateTables()
Dim conn As ADODB.Connection
Dim strPrimaryTbl As String
Dim strForeignTbl As String
On Error GoTo ErrorHandler
Set conn = CurrentProject.Connection
strPrimaryTbl = "myTbl"
strForeignTbl = "myTbl_Details"
conn.Execute "CREATE TABLE " & strPrimaryTbl & _
"(InvoiceId CHAR(15), PaymentType CHAR(20)," & _
" PaymentTerms CHAR(25), Discount LONG," & _
" CONSTRAINT PrimaryKey PRIMARY KEY (InvoiceId));", _
adExecuteNoRecords
conn.Execute "CREATE TABLE " & strForeignTbl & _
"(InvoiceId CHAR(15), ProductId CHAR(15)," & _
" Units LONG, Price MONEY," & _
"CONSTRAINT PrimaryKey PRIMARY KEY (InvoiceId, ProductId)," & _
"CONSTRAINT fkInvoiceId FOREIGN KEY (InvoiceId)" & _
"REFERENCES " & strPrimaryTbl & _
" ON UPDATE CASCADE ON DELETE CASCADE);", _
adExecuteNoRecords
Application.RefreshDatabaseWindow
ExitHere:
conn.Close
Set conn = Nothing
Exit Sub
ErrorHandler:
Debug.Print Err.Number & ":" & Err.Description
Resume ExitHere
End Sub
</source>

==Using a CHECK Constraint to Specify a Condition for All Values Entered for the Column==



<source lang="vb">

Sub CheckColumnValue()
Dim conn As ADODB.Connection
On Error GoTo ErrorHandler
Set conn = CurrentProject.Connection
conn.Execute "CREATE TABLE myTable(Id AUTOINCREMENT CONSTRAINT PrimaryKey PRIMARY KEY," & _
"CountMe INT, CONSTRAINT FromTo CHECK (CountMe BETWEEN 1 AND 30));"
ExitHere:
conn.Close
Set conn = Nothing
Exit Sub
ErrorHandler:
Debug.Print Err.Number & ":" & Err.Description
Resume ExitHere
End Sub
</source>

← Предыдущая	Версия 16:33, 26 мая 2010
(нет различий)

VBA/Excel/Access/Word/Access/Column Constraint - История изменений

в 16:33, 26 мая 2010

Admin: 1 версия