CODEWITHSUNDEEP
ms-accessjoinvbaforeign-keys
davidzxc574
davidzxc574

Reputation: 491

How to change join type when creating table relations, Access VBA

I have created relations between tables in Access vba with codes add constraint ...foreign key...references... But it does not do anything with the join type as the default join type remains inner join.

Table relations, join type

How can I define join type in Access vba when creating relations between tables?

thanks

Upvotes: 1

Views: 411

Answers (1)

Erik A
Erik A

Reputation: 32682

If you want to specify such things, you need to use DAO, and not SQL.

Sample code to create a relation that right joins Table1 to Table2 on a field named ID in both tables, without referential integrity:

Dim rel As New Relation
Dim db As DAO.Database
Set db = CurrentDb()
rel.Attributes = dbRelationDontEnforce + dbRelationRight
rel.Name = "MyRelation"
rel.Table = "Table1"
rel.ForeignTable = "Table2"
Dim fld As DAO.Field
Set fld = rel.CreateField("ID")
fld.Name = "ID"
fld.ForeignName = "ID"
rel.Fields.Append fld
db.Relations.Append rel

Upvotes: 2

Related Questions