How do I create a relationship between two tables in VBA

[ianclegg]

I have 2 tables in an access database, one is real and one is linked (docmd.transferdatabase aclink) the link & unlinking of this table works fine.

However I need to create a relationship of One-to many between the two tables, to do some reporting.

I have tried with createrelation but have been unsuccessful, can anyone help?

The details are as follows.

AccountNos Hazlehead
One Many
ServNo----------------Servno

Ian

 

[willem]

dim tbl1 as tabledef
dim tbl2 as tabledef
dim wks as workspace
dim db as database
dim relnew as relation
Set wks = Workspaces(0)
Set DB = wks.OpenDatabase("c:\documents\db1.mdb")
With DB
' tbl1 already exists as table , you have to create an instance of its tabledef
Set tbl1 = DB.TableDefs("tblname")

'tbl 2 is a tabledef you just created
Set relnew = .CreateRelation(relname(j), tbl1.Name, tbl2.Name)
relnew.Fields.Append relnew.CreateField("table1id") 'the primary key of the base table
relnew.Fields!table1id.ForeignName = "table2id"

.TableDefs.Append tbl2


.Relations.Append relnew

.Close
End With

willem


[This message has been edited by willem (edited 07-14-2001).]

 

[Pat Hartman]

You don't need to create relationships in the relationship window except to enforce referential integrity. And you can't do that when the tables reside in different databases.

Just create queries that include the tables you need and draw join lines between the linking fields.