Create relationship by code 1

[cbsm]

Hi,
Is it possible (if it is , then how ?) to create a relationship between tables through code ?

Background :
I need to create & delete tables through code.
This tables are used in "complex" queries.
Without this relationships - the queries are not updatable !

Thanks,

 

[PHV]

Why not deleting the records instead of the tables ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[wemeier]

You can create relationships using VBA code. Try the following:

Sub CreateRelationship()
	Dim db As Database, rel As Relation, fld As Field
	
	' Return reference to current database.
	Set db = CurrentDb()
	' Create new Relation object ("OrderHeaderOrderDetails"), 
            ' identify the primary table ("OrderHeaders") 
            ' and specify the foreign table ("OrderDetails").
	Set rel = db.CreateRelation("OrderHeaderOrderDetails", "OrderHeaders", "OrderDetails")
	' Set attributes to enforce referential integrity.
	rel.Attributes = dbRelationUpdateCascade And dbRelationDeleteCascade
	' Create the field in the Relationship object.
	Set fld = rel.CreateField("OrderID")

          ' Specify the field name in the foreign table.
	fld.ForeignName = "OrderID"
	' Append the Field object to the Fields collection of Relationship object.
	rel.Fields.Append fld
	' Append the Relationship object to the Relations collection.
	db.Relations.Append rel
	db.Relations.Refresh
	Set db = Nothing
End Sub

Cruising the Information Superhighway
_{(your mileage may vary)}

 

[BNPMike]

Without this relationships - the queries are not updatable

Relationships can't affect queries - tell us more about your query and what happens. Preferably cut and paste the SQL.