Unanswered: How do I create a relationship between tables during run-time?
As the title suggests, I need to know how to create a relationship between two tables during run-time in Access VBA. It might be worth noting that one the tables to be linked will be created during run-time (however, the table name and attributes will be known at run-time). I'm a bit of a novice on VB and SQL so go easy on that kinda stuff ok!!
Here is some code I sometimes use to create relationships. I store the column data in a table called TCAddField. I have included my comments of the parameter values which were defined in online help. If you need to make a relationship between more than 2 columns, then you will have to modify the code. rs!NewName is just a column in the table that I am now using for multiple purposes. It originally was created for holding the new name of a column that is to be renamed.
Dim rel As Relation
'For relationships, use the TCAddField columns as below:
' Code = unique relationship name
' dbName = the name of the database as per usual
' TableName = name of the primary table
' FieldName = name of the field in the primary table
' ForeignTable = name of the foreign table
' ForeignField = name of the field in the foreign table
' RelAttr = number for relationship attributes
Set dbs = DBEngine.Workspaces(0).OpenDatabase(DataDir & rs!DbName)
'Set relNew = dbs.CreateRelation(rs!Code, _
rs!tablename, rs!NewName, dbRelationUpdateCascade)
'dbRelationUnique 1 The relationship is one-to-one.
'dbRelationDontEnforce 2 The relationship isn't enforced (no referential integrity).
'dbRelationInherited 4 The relationship exists in a non-current database that contains the two linked tables.
'dbRelationUpdateCascade 256 Updates will cascade.
'dbRelationDeleteCascade 4096 Deletions will cascade.
' Find existing EmployeesOrders relation.
On Error Resume Next
For Each rel In dbs.Relations
If rel.Table = rs!tablename And rel.ForeignTable = rs!ForeignTable Then
'Error 3262 - Couldn't lock table...; currently in use by user 'Admin' on machine...
Set rel = dbs.CreateRelation(rs!Code)
rel.Table = rs!tablename ' Set Table property.
rel.ForeignTable = rs!ForeignTable ' Set ForeignTable property.
' Set Relation object attributes to enforce referential integrity.
If rs!RelAttr = 4352 Then
rel.Attributes = dbRelationDeleteCascade And dbRelationUpdateCascade
rel.Attributes = rs!RelAttr
Set Fld = rel.CreateField(rs!FieldName) ' Create field in Fields collection of Relation.
Fld.ForeignName = rs!ForeignField ' Provide name of foreign key field.
rel.Fields.Append Fld ' Append field to Relation and Relation to database.