Unanswered: URGENT!! Create table relationships during VBA runtime
Hi there folks,
As the title suggests, this is a really urgent issue for an assignment of mine thats due in a couple of weeks!
I need some code, or an object that will let me create a relationship between two tables (one created at run-time via SQL), and another already implemented in the system, all during run-time of the system.
Again this is a real important issue, someone tried to help me earlier posting the following code, which unfortunately doesn't work, as I suspect it depends on external module's etc. Feel free to look at the code, adjust so that it's self supporting!
'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.
Many many thanks to anyone that can help me in this matter. Links to other pages, modules, code, anything would be greatly appreciated!!!