Mega Code Archive
Relating Two Tables and Setting up Cascading Referential Integrity Rules
<![CDATA[
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
]]>