Creating relations between linked tables.

[alexrs2k]

I have this code to automatically create relations in a db with link tables
when the Field names are the same for different tables from SQL ODBC
conection but when it gets to "db.Relations.Append rel" gives an error:
"Can't create the relation in a link table". Any I deas? Thank you.

Sub CreateRelations()
Dim db As Database
Dim tb As TableDef, tb1 As TableDef
Dim rs As Recordset
Dim rel As Relation
Dim fld As Field, fld1 As Field
Dim sFieldName As String

Set db = CurrentDb()

For Each tb In db.TableDefs
For Each fld In tb.Fields
For Each tb1 In db.TableDefs
If tb.Name <> tb1.Name Then
For Each fld1 In tb1.Fields
If fld.Name = fld1.Name Then
Set rel = db.CreateRelation(tb.Name & tb1.Name,
tb.Name, tb1.Name, dbRelationInherited)
rel.Fields.Append rel.CreateField(fld.Name)
sFieldName = fld.Name
rel.Fields(sFieldName).ForeignName = fld.Name
db.Relations.Append rel
End If
Next 'fld1
End If
Next 'tb1
Next 'fld
Next 'tb
End Sub

 

[Dirk Goldgar]

I have this code to automatically create relations in a db with link
tables
when the Field names are the same for different tables from SQL ODBC
conection but when it gets to "db.Relations.Append rel" gives an error:
"Can't create the relation in a link table". Any I deas? Thank you.

Sub CreateRelations()
Dim db As Database
Dim tb As TableDef, tb1 As TableDef
Dim rs As Recordset
Dim rel As Relation
Dim fld As Field, fld1 As Field
Dim sFieldName As String

Set db = CurrentDb()

For Each tb In db.TableDefs
For Each fld In tb.Fields
For Each tb1 In db.TableDefs
If tb.Name <> tb1.Name Then
For Each fld1 In tb1.Fields
If fld.Name = fld1.Name Then
Set rel = db.CreateRelation(tb.Name & tb1.Name,
tb.Name, tb1.Name, dbRelationInherited)
rel.Fields.Append rel.CreateField(fld.Name)
sFieldName = fld.Name
rel.Fields(sFieldName).ForeignName = fld.Name
db.Relations.Append rel
End If
Next 'fld1
End If
Next 'tb1
Next 'fld
Next 'tb
End Sub

You can't create relationships between linked tables -- these relationships
must be created in the back-end database.

 

[Dirk Goldgar]

I just want to retrieve information from the linked tables (readonly
access).
There are a lot of link tables from the SQL DB( >100). I created some
relations manually and it worked (I created some queries and the relations
were there), but creating relations manually for that huge amount of
tables
is very difficult. That's why i tried using VBA to create them. Thank you.

I can't recall what happens when you manually create a relationship between
linked SQL Server tables. Certainly the relationship can't be enforced by
Access, but the relationship may be created as a guide to the query
designer, which may be what you're looking for. What you report makes me
think that you ought to be able to create unenforced, inherited Relation
after all.

I don't have an instance of SQL Server running to test this at the moment,
but would your code work, at least partially, if you applied both the
dbRelationInherited and dbRelationDontEnforce attributes? That is,

Set rel = db.CreateRelation( _
tb.Name & tb1.Name, tb.Name, tb1.Name, _
dbRelationInherited + dbRelationDontEnforce)

It seems to me that your logic is still flawed, as it seems to me that, by
looping through all tabledefs, you would be creating reciprocal
relationships. That is, you would create a primary-to-foreign relationship
between Table1.FieldA and Table2.FieldA, and then subsequently create a
primary-to-foreign relationship between Table2.FieldA and Table1.FieldA.
That certainly can't be right. You'd need to determine which table is
actually the primary and which is the foreign table, and only create the
relationship in one direction.