create relationship by code ? 1

[primerov]

Is there any possibility to link 2 tables by a function?
I have 2 tables, Customers and CallCustomers. I create the relationship between the field CallId(autonumber) in the table
CallCustomers and the field Calls in the table Customers in one to many, by following the instructions of the wizard.
Is there any way to build a code and create this relationship ?

 

[BillPower]

Dim dbs As DAO.Database
Dim tdf1 As DAO.TableDef
Dim tdf2 As DAO.TableDef

This will create a Relationship between Table1 and Table2 both having a Field named Field1:

Dim rel As DAO.Relation
Set dbs = CurrentDb
With dbs
Set tdf1 = .TableDefs!Table1
Set tdf2 = .TableDefs!Table2
Set rel = .CreateRelation("Field1Relationship", tdf1.Name, tdf2.Name, dbRelationDontEnforce)
rel.Fields.Append rel.CreateField("Field1&quot
rel.Fields!Field1.ForeignName = "Field1"
.Relations.Append rel
.Close
End With

Sorry, primerov wasn't sure of your Table Names and Fields, so will leave it up to you to edit Code.

Good Luck

Bill

 

[primerov]

Thank you very much for your reply.It works !!The code you have suggested works, however i obtain indeterminate relationship, while if i follow the wizard between the two tables, i receive one-to-many relationship.Could you help me further ? I need a one to many relationship.
Best regards


Below is the code i have applied:
Dim dbs As DAO.Database
Dim tdf1 As DAO.TableDef
Dim tdf2 As DAO.TableDef


Dim rel As DAO.Relation
Set dbs = CurrentDb
With dbs
Set tdf1 = .TableDefs!customers
Set tdf2 = .TableDefs!CallsCustomers
Set rel = .CreateRelation("Field1Relationship", tdf1.Name,

tdf2.Name, dbRelationDontEnforce)
rel.Fields.Append rel.CreateField("CallID&quot
rel.Fields!CallID.ForeignName = "CallID"
.Relations.Append rel
.Close
End With

P.S. The field CallID in the table CallsCustomers is an autonumber,
and the field CallID in the table Customers is a number.

 

[JamieDeVry]

That was a good solution. You can use DAO or ADO to program relationships. However, I prefer to use the old fashion embedded SQL solution.


This first procedure is used to create a Validation table:

Sub mcrCreateMemberType()
'MEMBER TABLE
strTable = "CREATE TABLE tlkpMemberType " & _
"(" & _
"MemberTypeID COUNTER PRIMARY KEY, " & _
"MemberType CHAR(6), " & _
"Description MEMO" & _
""

'execute command
With DoCmd
.SetWarnings False
.RunSQL strTable
.SetWarnings True
End With
End Sub


My next procedure is used to create a new Base table where references are made including referential integrity imposition not described in your other response.

Sub mcrCreateMember()
'MEMBER TABLE
strTable = "CREATE TABLE tblMember " & _
"(" & _
"AOA VARCHAR(6), " & _
"MemberTypeID INTEGER, DuesTypeID INTEGER, MailTypeID INTEGER, " & _
"PrefixID INTEGER, memFirstName CHAR(20) NOT NULL, memMiddleName CHAR(20), " & _
"memLastName CHAR(20) NOT NULL, " & _
"SuffixID INTEGER, Gender CHAR(1), memAddress1 CHAR(50) NOT NULL, " & _
"memAddress2 CHAR(50), ZipID INTEGER, memPhone CHAR(7), " & _
"memFax CHAR(7), memCell CHAR(7), memEMAIL CHAR(30), memWebPage CHAR(30), CollegeID INTEGER, " & _
"YearGraduated CHAR(4), YearResidencyCompleted CHAR(4), ResidencyHospital CHAR(30), " & _
"InternshipHospital CHAR(30), DateOfBirth DATE, " & _
"SpouseFullName CHAR(50), JournalMail INTEGER, NJACOFP YESNO, Notes MEMO, PRIMARY KEY (AOA), " & _
"FOREIGN KEY (MemberTypeID) REFERENCES tlkpMemberType" & _
""

'execute command
With DoCmd
.SetWarnings False
.RunSQL strTable
.SetWarnings True
End With

End Sub

This way, I keep my SQL skills sharp and still take care of my automated tables, queries, and relationships. ADO, DAO, and Embedded SQL are all great solutions.

Jamie 'DeVry'

 

[BillPower]

Hi JamieDeVry I like your solution too, but if primerov changes dbRelationDontEnforce to dbRelationUpdateCascade, should do what they want.

Bill